table design

[slimjen]

all. using access 2003. I created a database with 3 tables ive been struggling with. I also created a form with 2 subforms. The main table consist of the invoice id(pk) and invoicenum(text field no dups). The second table consist of tracking id(pk), trackingnum (text field no dups) invoicenum(not indexed)(from invoice table 1:many). Third table AcctID(pk), trackingnum(from tracking table 1:1) Cost field, AccountCode.
For every invoice you can have many trackingnum.
For every trackingnum you can have 1 acctid.
The main form conisist of a combo box with Invoicenum. ; i've created a tracking subform so When users select the invoicenum all the cooresponding tracking numbers are listed in datasheet. So far everything was working until I tried to add the accounting info. It needs to show in the 2nd subform one tracking number to one acct info. I can only get info in datasheet form with multiple tracking numbers. it doesnt cooresponding to a single record. I ve seen it done in ex. use cust, order and product tables but I cannot get it. Please help!

[June7]

Probably the query jointype needs to be changed. Can you provide the project for analysis? Make copy, remove confidential data, compact & repair, zip if still large.

[slimjen]

Sorry; thought I attached a database. I am think the accounting table info needs to be combined with the shiptment table because the accounting table only provides additionas detail info. Can I exclude the accounting info from the subform and have it on the same form referenced back to the subform somehow? Please take a look and let me know.
Thanks

[June7]

When opening frmSMALLPACKAGEFORM, got parameter prompt. Found it in the OrderBy property of Shipment subform and removed the SQL statement.

The jointype in Relationships for tbl_SP_SHIPMENT and tbl_SP_ACCOUNTING is 'Only...', in the qrySHIPACCT it is 'Show all from tbl_SP_SHIPMENT...' but that's probably not an issue.

Do you want the Accounting subform to show record that relates to the selected tracking number record in the Shipment subform?

[slimjen]

yes. This is my issue. the accounting subform is not displaying the record that relates to the tracking number record. when I select the tracking number it tells me it will duplicate record. not trying to create recrd.thks

[June7]

Then Accounting subform is dependent on the Shipment subform record, linking on the tracking number. I know how to do a form/subform/subsubform arrangement. Getting a subform to link sideways to another subform is not easy. Only seen it once. Can't find it now and not having luck on my own. Here is possible solution to sideways link http://www.access-programmers.co.uk/...ad.php?t=45857 and http://www.fmsinc.com/microsoftacces...edsubforms.asp
Also, these subforms do not need to include table joins. Just set the RecordSources to the tables, use the OrderBy property