transaction table probs.

[holden_1]

Hi,

I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:

(please see attached .jpg)

(hope that makes sence)... so effictively I have a many to many relationship.

I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?!
I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID.
Any help is much appriciated!

Next job...

I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part).
See what I'm getting at?
I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy!
I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!

Many Thanks

Rob

 

[holden_1]

Thanks pat... but i'm still stuck!

I've attached a better picture this time - showing the whole structure.

Have I now created transaction tables correctly?

What I need is for the user, in tbl_Parts, to be able to select one or more Supplier for that part. This Supplier(s) need to be selected by Supplier Name/Number, not the SupplierID - which is a meaningless PK. I assume these SupplierID(s) will be stored against PartID in the transaction table.
Transaction table -

SupplierPartID*
PartID*
SupplierID*

Is this the correct setup?

Thanks again for any help.

 

[holden_1]

OK thats helpful - i now understand the critical properties for a combo box. However, this does not store the supplier for a part anywhere but in the combo box on that form! Obviously this needs to be stored in tbl_Supplier_Part no? or am I missunderstanding the concept here? Do I store this data in tbl_Supplier_Part with an update query? I'm stuggling here a little!

Attached is a revised structure as you suggested (the CR is for a part from a particular supplier).

Cheers

Rob

 

[holden_1]

Awsome Pat! Think I've cracked it!! Combo box on a subfor for Parts - you select one or more suppliers and then bosh, its saved in tbl_Supplier_Parts. Magic! (I haddent appricated how your queries interacted with your forms - ie. if the query has the field "part number" it takes this from the Part number in the form eh)

Will continue to play with it and sort out the other many to many's, will keep you posted.

Best Regards,

Rob