Hi,
I am trying to create a similar db to this: http://www.access-programmers.co.uk/learning2/sample3.htm
Looked and there is no video
I have made a start with two tables, 1 is customer and the other is service_orders.
I have a 1 to many relationship from customer (key) field 'cust_no' (which is an auto number) to service_orders field 'cust_no' (which is a number type).
I want a form where customers can be viewed/added (if they added I don't want duplicates) and also to see all the customers service orders in maybe a list so they can be selected.
I am just looking for some advice and possibly help on queries. I currently have a query for service order selection which works but when tied to a button I have to enter the customer ID instead of it taking it from the customer ID field on the form. The SQL looks like:
SELECT Service_Order.Service_Order_No, Service_Order.Date_in, Service_Order.Problem_Description, Service_Order.Parts_Used_1, Service_Order.Quantity_1, Service_Order.Price_1, Service_Order.Parts_Used_2, Service_Order.Quantity_2, Service_Order.Price_2, Service_Order.[Parts Used_3], Service_Order.Quantity_3, Service_Order.Price_3, Service_Order.Parts_Used_4, Service_Order.Quantity_4, Service_Order.Price_4, Service_Order.Parts_Used_5, Service_Order.Quantity_5, Service_Order.Price_5, Service_Order.Parts_Used_6, Service_Order.Quantity_6, Service_Order.Price_6, Service_Order.Parts_Used_7, Service_Order.Quantity_7, Service_Order.Price_7, Service_Order.Parts_Total, Service_Order.VAT, Service_Order.Total, Service_Order.Deposit, Service_Order.Comments
FROM Service_Order
WHERE service_order.cust_no=form.custnumtxt;
Also this just brings it up in cell format I'd ideally like it in a formatted form....
Thanks in advance for any help!!
I am trying to create a similar db to this: http://www.access-programmers.co.uk/learning2/sample3.htm
Looked and there is no video
I have made a start with two tables, 1 is customer and the other is service_orders.
I have a 1 to many relationship from customer (key) field 'cust_no' (which is an auto number) to service_orders field 'cust_no' (which is a number type).
I want a form where customers can be viewed/added (if they added I don't want duplicates) and also to see all the customers service orders in maybe a list so they can be selected.
I am just looking for some advice and possibly help on queries. I currently have a query for service order selection which works but when tied to a button I have to enter the customer ID instead of it taking it from the customer ID field on the form. The SQL looks like:
SELECT Service_Order.Service_Order_No, Service_Order.Date_in, Service_Order.Problem_Description, Service_Order.Parts_Used_1, Service_Order.Quantity_1, Service_Order.Price_1, Service_Order.Parts_Used_2, Service_Order.Quantity_2, Service_Order.Price_2, Service_Order.[Parts Used_3], Service_Order.Quantity_3, Service_Order.Price_3, Service_Order.Parts_Used_4, Service_Order.Quantity_4, Service_Order.Price_4, Service_Order.Parts_Used_5, Service_Order.Quantity_5, Service_Order.Price_5, Service_Order.Parts_Used_6, Service_Order.Quantity_6, Service_Order.Price_6, Service_Order.Parts_Used_7, Service_Order.Quantity_7, Service_Order.Price_7, Service_Order.Parts_Total, Service_Order.VAT, Service_Order.Total, Service_Order.Deposit, Service_Order.Comments
FROM Service_Order
WHERE service_order.cust_no=form.custnumtxt;
Also this just brings it up in cell format I'd ideally like it in a formatted form....
Thanks in advance for any help!!