Subform or Query for this 2 table, 1 Form solution

[shelzmike]

I think that I have my database designed okay...I have two tables that reside in my accdb database that have the following structure:



mifi_ESN (PK), techNum(FK), SSID, mifiPass, mifiIsAssigned

And next table

LaptopSN (PK), techNum (FK), laptopIsAssigned

Basically this is keeping track of our Verizon MiFi hotspots and the other is keeping track of laptops. I kept them separate since, well they could be mixed and matched in any instance.

Oh, I forgot, I have another table (really mostly a lookup table) with the tech info, on which the techNum is the PK.

As you may see, the mifi and laptop tables will never see each other except in a query that will use all three of the mentioned table with techNum being the lynchpin so to speak.

So, I want to create a form on which I can enter (and therefore bind) all fields from the mifi and laptop tables. I only want one form, but cannot figure out how to handle the fact that techNum is in both tables, needs to be stored in both, but I want to be able to have the user only enter it once, does that make sense?

So should I use a query or subform for this setup. This is really more for my use only so it will be more functional and quick than neccesarily pretty. How would I handle the updating of both tables with the techNum in either situation? Thanks.

Mike

[June7]

Will each tech have only one MiFi and one Laptop? If so, think need another table.

table of MiFi info
table of Laptop info
table of Tech info

junction table
TechID MiFiID LaptopID

[shelzmike]

Thanks for the reply. However, I am slightly confused. I thought that junction tables were used in situations where you have a many to many relationship, which is obviously not desirable. Am I wrong? Why would I need that in this situation?

[June7]

Because you want want one form and enter tech number once.

This tracks which techs have which equipment. The equipment tables end up being lookup info tables.

Can each tech have only one MiFi and one Laptop?

duplicate/related topic thread by same OP https://www.accessforums.net/databas...ign-14806.html