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

 

[Pat Hartman]

The picture doesn't include the transaction table but it looks like the transaction table is related to the parts table. I think that since you want to specify which supplier for the part, the transaction table should be related to the supplier part table and instead of storing the individual supplier and part id's, you should store the supplier part ID in the transaction table.

Take a look at my Many-to-Many sample database.

 

[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.

 

[Pat Hartman]

I the complaint about the part or about the part for a particular supplier? If the latter, the relationship needs to be between CRID and supplierPartID rather than CRID and PartID.

To make combos correctly, you need to use queries as their RowSources. So create a query that selects the SupplierID and his name from the supplier table. Order it by the supplier name. Use this query as the RowSource for the supplier combo. Pay attention to the following combo properties:

RowSource = some query name
ColumnCount = # of columns in the query, in this case 2.
BoundColumn = the column number of the unique identifier, in this case column 1 is the supplierID and so that will be the bound column.
ColumnWidths = the width of each column. 0 will hide a value so to hide the ID make the ColumnWidths = 0",2"

These 4 settings are CRITICAL to the proper operation of your combos.

A combo defined this way will SHOW the supplier name but STORE the supplierID which is correct.

 

[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

 

[Pat Hartman]

You really need to examine the example I referred you to. The subforms are what create the rows in the relation table which is I think what you are having trouble with. Look at the RecordSource for the main form and then look at the RecordSource for the subform. In the subform, you choose the record you want to relate the mainform record to from a combo. So for example if you want to relate a supplier to a part, use part as the recordSource for the main form. The subform is populated with a query of records from the junction table joined to the supplier table. By choosing a supplier from the combo in the subform, you complete the link of part to supplier and create a new relation record.

 

[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