98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Table relationships for split deliveries
Table relationships for split deliveries
Resolved · Medium Priority · Version 2002/XP
Wed 26 Nov 2014, 18:34Delegate Joe said...
Joe has attended:
Access Intermediate course
Table relationships for split deliveries
Dear BestSTL,
I have a table relationship set up*, not dissimilar to the one in the Microsoft Northwind example, where I'm dealing with Customers, orders and products.
The difference is stores are related to the order table through a contacts table.
I want to write comments about each order, currently held in TBL_STORES_Orders_Comments and to be able to write as many separate comments as I wish for each order; assigning the recipient of the comment as 'customer', 'production' & 'ROYAL BLUSH' (the name of our company). This way customer documents (delivery lists etc) will show only comments related to them, production will see only comments related to them and so on.
My problem is as soon as I enter a second or third comment for the same order, the query used for customer documents duplicates the products two or three times, depending on how many comments there are.
Although I haven't tried it yet, I'm sure the same problem will arise with TBL_STORES_Orders_Shipments. In an ideal World there would be 1 shipment per order, but sometimes some products have to be sent in advance creating a split delivery. Here again I need to be able to create as many shipments as needed for one order without duplicating the products on that order.
Any help on table relationships to resolve my problem would be appreciated.
Best regards
Joe
*(Please see accompanying email, info@stl-training.co.uk, for images)
Thu 27 Nov 2014, 21:18Trainer Jens said...
RE: Table relationships for split deliveries
Hi Joe,
Thank you for the forum question.
If I understand you right.
If you create a query based on one table (orders table) then the query will show one record for each record in the table if the query is not filtered. If you create a query based on two tables (orders and Comment table) and have a one to many relationship then the query without filter shows the number of records in the many table (comment table).
So if you have three comments for one order you will have the order information in the query three times. If you need to summarize each order or only want each order one time in the query you need to group your query using the totals button and the totals row.
I have created a tiny database where I have done the grouping. Please find attached.
I hope that this is what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
orders.mdbMon 1 Dec 2014, 06:59Delegate Joe said...
RE: Table relationships for split deliveries
Dear Jens,
Thank you for your answer & example.
I’ve been away for a couple of days from Friday, so I have to catch up then get back to looking at how I can make the query work in the way I need.
I’ll get back to you as soon as I can.
Best regards.
Joe
Wed 3 Dec 2014, 13:56Delegate Joe said...
RE: Table relationships for split deliveries
Dear Jens,
I've created a simple data base to illustrate what I want and the problems I face - Please see accompanying email to info@stl-training.co.uk
- The tables are straight forward enough, linked together in the relationships window. There may be none or many comments associated with an order. If there are any, they will be in TBL_STORES_Orders_Comments.
- The query (the basis for the report) has the same relationship set up as in the relationships window.
- The report I created shows one order per page with associated items on each order
Problem 1 - query.
The item records are duplicated for as many comments associated with the order (orders 14.12.001 & 14.12.002 duplicate all item records 3x, as there are 3 comments for each order). This is then reflected within the report where the wrong number of item lines display for orders 14.12.001 & 14.12.002). I saw your example with grouping, but this deletes the comments - I need them in the report too.
Problem 2 - report.
I need every comment associated with an order to display within the report, not just the first one Access comes to in the query.
I'm stuck as to how the tables should be related, or if related queries should be used to address the issues in problems 1 & 2.
Note: I've ignored other tables that were shown when I first submitted my question (e.g a shipments table) as I suppose once a solution is found the same principle can be applied to similar tables.
Best regards,
Joe
Fri 5 Dec 2014, 20:25Trainer Jens said...
RE: Table relationships for split deliveries
Hi Joe,
Please find attached Access database.
I have created a report based on tables not on queries. Sometimes it is best to base reports on queries sometimes tables.
I the report I have added sub reports showing the comments fro the orders.
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Sat 6 Dec 2014, 19:20Delegate Joe said...
RE: Table relationships for split deliveries
Dear Jens,
Thanks for your reply and example.
It just comes at a time when I’m going away for a week so I can’t look at it just now :(
I’ll keep the case open and will reply on my return in a week’s time.
Best regards.
Joe
Tue 16 Dec 2014, 06:30Delegate Joe said...
RE: Table relationships for split deliveries
Dear Jens,
Thank you for your example!
I've looked at it and see the answer to my problem seems to be to use sub reports based on dedicated tables/queries, and not to use a huge query that encompasses all required fields from all tables.
I guess the main change I would perform would be to still keep all comments in one table (with their recipient field) but use a query in the subreport to filter the ones I need. If I understand correctly, your method would need one table per recipient of comment. If the number of recipients is 3, it would be no problem, but through experience this nearly always changes further along the design process.
I might have to get back to you regarding how to organize split deliveries if it's not similar to the set up example you gave me.
In the meantime however, thank you for your help; I've certainly been pointed in the right direction.
Joe
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Zoom feature for queriesIf youare working on on query design and creating a calculated field, it can be hard to view the whole statement. You can hold the shift key down and press F2, to show the zoomed up version of the query field |
