Saving Data on Form to 3 tables


I am going through a thought process of designing this form:

Main Form containing 3 subforms  (

Form1 contains Name and ID info (Driver)
Form2 contains Name and ID info (say Co-Driver)
Form3 contains Various Vehicle Information.

I have 2 tables this information would go into:

Table1: Name, Address, ID info
Table2: Various Vehicle Information

Tables 1 and 2 are "One to Many" Relationship

Next I have Table3:  Will contain a combination of Form1, Form2, and Form3

Table3:  Name(s) Driver1,Driver2, ID Info Driver1, ID Info Driver2, Various Vehicle Info for (both) Driver(s)

I wanted a way to just use Table1 and Table2 and pull that information later and combine those on a report that has this:

Driver's Name:  Bob Smith and Joe Smith  
Vehicle Info; Some Truck and Trailer Info
Driver1_Id Info: 123456
Driver2_Id Info: 456789

Didn't know how I could do this other than creating a 3rd table to create the data combined and later pulling that data into a report.

Table3 I would:  Driver1_ID, Driver2_ID, Driver1_Fname, Driver1_LName, Driver2_FName, Drive2_Lname, Various Vehicle Info (several fields)

I would take the data on the form and save to two/three tables.

Or if there would be a better and more efficient way, please let me know,

All help is appreciated to do this right the first time.

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Your current design for Table3 needs tuning up.  I would approach it this way:

DriverID (PK)
<other driver attributes>

VehicleID (PK)
<other vehicle attributes>

VehicleDriverID (PK)
VehicleID (FK)
DriverID (FK)
DriverRole         <----- use this to indicate primary or secondary driver

tblVehicleDrivers thus becomes the junction table that enables the many-to-many relationship between drivers and vehicles.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggSecurity Management InfoSecAuthor Commented:

So on the 3rd table I have

6 unique fields that are interrelated to the other 2 tables.  Gotit

Now.  Each line or Row is unique to each entry.

What I want to do is pull on a report  These fields:

Combine these fields into one:  Primary Driver FName and LName, Secondary Driver FName and LName
Next Field:  Make
Next Field: Model
Field: StartTime
Field: EndTime

So It would look like this on the final report:

Driver Name(s)                          Make               Model     StartTime          EndTime
Bob Smith and Dana Smith        FrtLiner            3500        1500                   1600

All the while we notice there are separate entries in the table(s).  How can I take these separate entries and combine them (because they are together) on a report laid out as the above?
Ernest GroggSecurity Management InfoSecAuthor Commented:
Also on the Forms I have 3 forms

a form for

Primary Driver

one for

Secondary Driver

3rd form

Vehicle ID info

so they would be entering into the tables How?

The primary Driver and secondary driver are not related for primary and child fields but would have primary and child fields to the 3rd form.  (each would be linked to the 3rd form separatly?)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Ernest GroggSecurity Management InfoSecAuthor Commented:
The only way I could see this is to create a table with all the fields and then  from the form (a button perhaps), use VBA to save the form info to the table?

But I don't know how to do that....
Ernest GroggSecurity Management InfoSecAuthor Commented:
Private Sub CmdSave_Click()
 Dim mySQL As String
 mySQL = "INSERT INTO VGTruckTracking([PDriverFName],[PDriverLName])VALUES(" & Forms![Form1]![DriverFName] & "," & Forms![Form1]![DriverLName] & ");"
Debug.Print mySQL
DoCmd.RunSQL mySQL

End Sub

Open in new window

or something like this?:

INSERT INTO VGTruckTracking( Drivers, VehicleInfo) SELECT [Forms]![Emp_New]![PDriverFName] & [Forms]![Form1]![PDriverLName] & [Forms]![Form2]![SDriverFName] & [Forms]![Form2]![SDriverLName]  AS Drivers, [Forms]![Form3]![StartTime] AS StartTime;

Open in new window

I would need to add spaces, etc, but would this work too?
In most situations Access treats queries and tables interchangeably (that is why you can never give both a query and a table the same name).  So, any place you could use a table, you can use a query so there is rarely a need to create temporary tables such as you are describing.  Just create a query that joins the tables and use the query as the recordsource.

To get primary and secondary drivers into a single row, you can use a cross-tab query.  Then join the crosstab query to the driver and vehicle tables to pick up the descriptive information.
Ernest GroggSecurity Management InfoSecAuthor Commented:
Hello Pat,

Is there any way u could help me by giving me an example of this?  

I have been trying to get this but just not getting there.
I don't have time now but if you post your database, I may have time over the weekend.  Perhaps someone else might help in the meantime.
Ernest GroggSecurity Management InfoSecAuthor Commented:
OK, No problem

Thank you so much.
Jeffrey CoachmanMIS LiasonCommented:
From what I can tell, your design needs to be changed to something like what Dale suggested.

You really need to get the table design and relationships set in stone first, ...before every worrying about creating any forms...

Please also remember that we have no overall perspective of what this system will be tracking, we cannot be sure of the table entities...

For example:
Can One Primary Driver have many vehicles?
Can One Primary Driver have many Secondary drivers?
Can Secondary Drivers have Tertiary Drivers...?
Can Secondary Drivers also have their own Vehicles...?

If your primary table contains info about "Drivers, then name it tblDrivers (as Dale suggests)
The same goes for VGSponserLocationBadge, ...if this is info about Vehicles, ...then name it tblVehicles.
(A table name should not include the fields in the table: MGNameAddressPhone)

So again, without any context here, is hard to say what the table structure/relationships should be...

So first, ...please explain to us what this system will be tacking (with generic, non-industry specific terms), ...and what it's ultimate purpose/use will be.


Ernest GroggSecurity Management InfoSecAuthor Commented:
So, I did get what I was looking what I did was, use a form with searching the primary table:  tblDrivers, then saving data to a separate table that combines the info into one.

Really, not what I wanted but, still, I can retrieve the data for reporting.  Couldn't get that crosstab thing down.  Still don't get it.  

Perhaps somone would but a lesson together?
Jeffrey CoachmanMIS LiasonCommented:
Then, as far as I can tell, you should have accepted Dale's post

Please click the "Request Attention" link in your original Question, and ask that the accepted solution be changed to Dale's post
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.