I am tring to create a order form which uses information from my main table (Sales) but includes supplier address (SupplyAddress) and delivery address (DlyAddress). I have linked the 3 tables and can get information from Sales but not the other 2. By trying different things I can, for example, get information from SupplyAddress but then lose Sales data. The 3 address files will have SupplyAddress1, SupplyAddress2 etc/
Multiple tables
- Thread starter the bard
- Start date
I have tried everything!!!I have tried via a query which includes all the tables, I have tried creating a new form based on all the tables and I have tried sub-forms, but cannot get rid of heading etc on sub-form. I have created Look ups on the main (Sales) table to select relevant order, delivery address etc.
I think the problem is with my joins. I have 3 address tables - supplier, delivery and invoice. On the main table (Sales) I have look ups to allow me to insert the correct address. When trying to join the address tables to the Sales table, whatever I try Access throughs up an error message to the effect cannot use multiple choice as join.
neileg
AWF VIP
- Local time
- Today, 23:19
- Joined
- Dec 4, 2002
- Messages
- 5,975
Oh no! Table level lookups are a nightmare! Whoever added them to Access should be shot...
You should only have one address table with a field that identifies what kind of address it is. However, each of your tables should only be linked to a single field in the sales table.
If I'm going to be able to help, you need to tell me what you structure is, or preferably post a copy of your database here.
You should only have one address table with a field that identifies what kind of address it is. However, each of your tables should only be linked to a single field in the sales table.
If I'm going to be able to help, you need to tell me what you structure is, or preferably post a copy of your database here.
Here is the sql code. I am sure it is the joins that are wrong but... I originally wrote this programme in Amstrad Locoscript. Whilst I have a Windows version, it is not very happy and keeps crashing, plus it will not work on Vista when I eventually have to upgrade. Howver, it does absolutely everything I want (including look ups!).
SELECT Sales.OrderNo, Sales.Confirmation, Sales.Item1, Sales.Quantity1, Sales.Price1, Sales.Item2, Sales.Quantity2, Sales.Price2, Sales.Item3, Sales.Quantity3, Sales.Price3, Sales.Item4, Sales.Quantity4, Sales.Price4, Sales.Item5, Sales.Quantity5, Sales.Price5, Sales.Special, Sales.Invoiceno, Sales.ANoteAttached, Sales.CustOrderNo, Sales.Delivery, Sales.TC, Sales.PlainPack, Sales.Discount, Sales.Terms, Sales.ID, Sales.Total1, Sales.Total2, Sales.Total3, Sales.Total4, Sales.Total5, Sales.VAT, Sales.Grandtotal, Sales.Totalgoods, Sales.TotalQuantity, Sales.[Inv/Rcpt], Sales.SupplyID, Sales.DlyID, Sales.InvID, Sales.SupplyAddress1, Sales.SupplyAddress2, Sales.SupplyAddress3, Sales.SupplyAddress4, Sales.SupplyAddress5, SupplyAddress.ID, SupplyAddress.SupplyAddress1, SupplyAddress.SupplyAddress2, SupplyAddress.SupplyAddress3, SupplyAddress.SupplyAddress4, SupplyAddress.SupplyAddress5, SupplyAddress.SupplyID, DlyAddress.ID, DlyAddress.DlyAddress1, DlyAddress.DlyAddress2, DlyAddress.DlyAddress3, DlyAddress.DlyAddress4, DlyAddress.DlyAddress5, DlyAddress.SupplyID, DlyAddress.DlyID, InvAddress.ID, InvAddress.InvAddress1, InvAddress.InvAddress2, InvAddress.InvAddress3, InvAddress.InvAddress4, InvAddress.InvAddress5, InvAddress.InvID
FROM ((Sales INNER JOIN DlyAddress ON Sales.ID = DlyAddress.ID) INNER JOIN InvAddress ON Sales.ID = InvAddress.ID) INNER JOIN SupplyAddress ON Sales.ID = SupplyAddress.ID
ORDER BY Sales.OrderNo;
SELECT Sales.OrderNo, Sales.Confirmation, Sales.Item1, Sales.Quantity1, Sales.Price1, Sales.Item2, Sales.Quantity2, Sales.Price2, Sales.Item3, Sales.Quantity3, Sales.Price3, Sales.Item4, Sales.Quantity4, Sales.Price4, Sales.Item5, Sales.Quantity5, Sales.Price5, Sales.Special, Sales.Invoiceno, Sales.ANoteAttached, Sales.CustOrderNo, Sales.Delivery, Sales.TC, Sales.PlainPack, Sales.Discount, Sales.Terms, Sales.ID, Sales.Total1, Sales.Total2, Sales.Total3, Sales.Total4, Sales.Total5, Sales.VAT, Sales.Grandtotal, Sales.Totalgoods, Sales.TotalQuantity, Sales.[Inv/Rcpt], Sales.SupplyID, Sales.DlyID, Sales.InvID, Sales.SupplyAddress1, Sales.SupplyAddress2, Sales.SupplyAddress3, Sales.SupplyAddress4, Sales.SupplyAddress5, SupplyAddress.ID, SupplyAddress.SupplyAddress1, SupplyAddress.SupplyAddress2, SupplyAddress.SupplyAddress3, SupplyAddress.SupplyAddress4, SupplyAddress.SupplyAddress5, SupplyAddress.SupplyID, DlyAddress.ID, DlyAddress.DlyAddress1, DlyAddress.DlyAddress2, DlyAddress.DlyAddress3, DlyAddress.DlyAddress4, DlyAddress.DlyAddress5, DlyAddress.SupplyID, DlyAddress.DlyID, InvAddress.ID, InvAddress.InvAddress1, InvAddress.InvAddress2, InvAddress.InvAddress3, InvAddress.InvAddress4, InvAddress.InvAddress5, InvAddress.InvID
FROM ((Sales INNER JOIN DlyAddress ON Sales.ID = DlyAddress.ID) INNER JOIN InvAddress ON Sales.ID = InvAddress.ID) INNER JOIN SupplyAddress ON Sales.ID = SupplyAddress.ID
ORDER BY Sales.OrderNo;
neileg
AWF VIP
- Local time
- Today, 23:19
- Joined
- Dec 4, 2002
- Messages
- 5,975
Several things here.
Joins first. Because you have used inner joins you will only get results if there is at least one matching record in every table. That's probably why you're not getting any results. Right click on the joins in the query grid and change them to joins that return all of the records from Sales and those records from the address tables that match.
Structure next. You have an un-normalised structure. As I already said, you should only have one address table. The Sales table is worse, though. Instead of having Sales.Item1 Sales.Quantity1, Sales.Price1, Sales.Item2, Sales.Quantity2, Sales.Price2, Sales.Item3, Sales.Quantity3, Sales.Price3, Sales.Item4, Sales.Quantity4, Sales.Price4, Sales.Item5, Sales.Quantity5, Sales.Price5, these should be separate records in another table. The usual approach for invoices, orders and similar objects is to use a table for the header details and a separate table for the individual lines.
You need to read up on mormalisation. There stuff in these forums or try Wikipedia
Joins first. Because you have used inner joins you will only get results if there is at least one matching record in every table. That's probably why you're not getting any results. Right click on the joins in the query grid and change them to joins that return all of the records from Sales and those records from the address tables that match.
Structure next. You have an un-normalised structure. As I already said, you should only have one address table. The Sales table is worse, though. Instead of having Sales.Item1 Sales.Quantity1, Sales.Price1, Sales.Item2, Sales.Quantity2, Sales.Price2, Sales.Item3, Sales.Quantity3, Sales.Price3, Sales.Item4, Sales.Quantity4, Sales.Price4, Sales.Item5, Sales.Quantity5, Sales.Price5, these should be separate records in another table. The usual approach for invoices, orders and similar objects is to use a table for the header details and a separate table for the individual lines.
You need to read up on mormalisation. There stuff in these forums or try Wikipedia
the bard
I have changed the links. I originally had only 1 address table (as per my existing DOS programme) but I could not get it to enter 2 different addresses on an order form (SupplyAddress and DlyAddress). I therefore set up 3.
I have read up on normalisation and, I must admit, didn't understand very much!
I have changed the links. I originally had only 1 address table (as per my existing DOS programme) but I could not get it to enter 2 different addresses on an order form (SupplyAddress and DlyAddress). I therefore set up 3.
I have read up on normalisation and, I must admit, didn't understand very much!
neileg
AWF VIP
- Local time
- Today, 23:19
- Joined
- Dec 4, 2002
- Messages
- 5,975
Addresses first. I would have one table. In the data entry form, I'd create a combo box for each of the 3 addresses and base the combo box on a query that pulls the address data from the table. If you need to limit addresses to only one type, add a field that identifies the type in the table and select for this in the query.
Normalisation can become quite complex but its basic principle is that your structure should be as lean as possible, you hold data at the lowest level possible and you only hold data once.
So you don't have 5 sets of fields for sales becase you only need one set. You have this in a related table and you have one record for each sale. If you have one sale you have one record, if you have 25 sales you have 25 records.
The lowest level for data is the point at which further subdivision is meaningless. So if you have 25mm pink rope, you would store this in 3 fields, one for size, one for colour and one for type of product.
To avoid duplication means that there is a single record that records the attributes of the object and that you store the unique identifier of that record (the primary key) when you need to, not the related data. So you don't record a sale to ACME Limited you record a sale to customerID 10987.
Normalisation can become quite complex but its basic principle is that your structure should be as lean as possible, you hold data at the lowest level possible and you only hold data once.
So you don't have 5 sets of fields for sales becase you only need one set. You have this in a related table and you have one record for each sale. If you have one sale you have one record, if you have 25 sales you have 25 records.
The lowest level for data is the point at which further subdivision is meaningless. So if you have 25mm pink rope, you would store this in 3 fields, one for size, one for colour and one for type of product.
To avoid duplication means that there is a single record that records the attributes of the object and that you store the unique identifier of that record (the primary key) when you need to, not the related data. So you don't record a sale to ACME Limited you record a sale to customerID 10987.
I totally agree with NeilG. One table only. All You have to do is flag each Record with the Type of Address S / I / D that is if the Address details are variable.
If the addresses are constant and Deliveries are sourced from the Supplier and sent directly to the Customer. Combi-box for Supplier (Pickup from). The Invoice address (Bill to) and Delivery Address (Delivery to) should be either on Customer record or linked into the Customer Record. On your documentation, I would use SubReports to attach the address information to the main body of the document.
There is no need to get involved with the Address details in the Sales Query at all.
Now if the addresses are variable, the principle is exactly the same except these tables are associated with Sales and hold the additional information of Sales.OrderNo.
Simon
If the addresses are constant and Deliveries are sourced from the Supplier and sent directly to the Customer. Combi-box for Supplier (Pickup from). The Invoice address (Bill to) and Delivery Address (Delivery to) should be either on Customer record or linked into the Customer Record. On your documentation, I would use SubReports to attach the address information to the main body of the document.
There is no need to get involved with the Address details in the Sales Query at all.
Now if the addresses are variable, the principle is exactly the same except these tables are associated with Sales and hold the additional information of Sales.OrderNo.
Simon
The supplier address(es) would be only 3 or 4. But delivery and invoice adresses - which may be different - could be up to 100 over a year as goods are supplied to (hopefully) new customers. There will however be some regular delivery and invoice addresses. I therefore need the facility to use regular and to input new addresses.
Customers can order several different items (hence item 1, 2 etc) and the 'quantity' and 'price' entries will be used in calculations in the invoice form. The idea is to have just the one table to enter all these details in one go and then link to address table to create order, advice note and invoice.
Customers can order several different items (hence item 1, 2 etc) and the 'quantity' and 'price' entries will be used in calculations in the invoice form. The idea is to have just the one table to enter all these details in one go and then link to address table to create order, advice note and invoice.
I have been trying your various suggestions, with some progress! I have created a combo box to pull addresses from a single address table, but it displays the whole address across the page, ie not on several lines as per normal addresses. How can I achieve this?
neileg
AWF VIP
- Local time
- Today, 23:19
- Joined
- Dec 4, 2002
- Messages
- 5,975
I think I know what you mean. I would reformat the combo to show only enough of the address to be sure that it is the correct address. If you want the whole address on the form, I would use a series of text boxes to pick up the data from the combo box columns.
My Address table contains (say) 5 lines of a customer's address. Some will be very short (ABC Ltd) and some will be very much longer. What I need is a way of laying out the address exactly as if you were addressing a letter ie on up to 5 separate lines. I need to be able to select line 1 with the combo box and the rest of the lines are displayed, one under each other.
the bard
This is frustrating! Thanks to all your help I have now got the form nearly right, except I can only display one line of the address via the combo box. Surely I cannot be unique in wanting to put a postal address on a form?
This is frustrating! Thanks to all your help I have now got the form nearly right, except I can only display one line of the address via the combo box. Surely I cannot be unique in wanting to put a postal address on a form?
