Clients & Orders db (1 Viewer)

[Housey]

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!!

 

[Mile-O]

Never mind queries for now. You need to get your table structure correct.
What happens if someone wants to order eight separate items? Nine? Ten? One hundred? That's right, you'd need to open up your table, add new fields which would cause problems throughout your database as you would then have to update all your queries to accommodate this and then redesign your forms to interact with the new fields, and then work with the new fields to implement them in reports...and so on and so forth.

The correct procedure is to normalise your database which you have not done.

The repeating group (i.e. 1, 2, 3, 4, 5, 6, 7) is a database no, no (with the exception of building an address) and, when you have such a repetition it becomes a sign that you require a further table in your database - they should grow down (in records) and not across (in fields).

So, while you have made a one-to-many relationship with your database (one customer can order many services) what you really have is a many-to-many relationship that you need to simulate (many customers can order many services).

I don't know much about orders databases but I'd advise you look at the Northwind database (ships with Access or is downloadable here for A97 or here for A2000+) since that shows a proper way to design this type of database.

 

[Housey]

What can I say?

You have pointd me in the right direction.. and I thank you for that... it's logical and I should've seen it

I am writing this for a small computer shop, that does repairs, etc etc

I couldn't see anyone needing anymore that 7 parts or labour items (in this small business) BUT even so.. it needs to be thought of and is more managable when implemented.

I will introduce a new table and relationship and see where I go from there.

Many, many thanx and i'll try and let u know how it goes

cya

Housey