asked on

Saving Data on Form to 3 tables

Hello,

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.

Thanks!

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

military donut

ASKER

OK...

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?

military donut

ASKER

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?)

military donut

ASKER

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

military donut

ASKER

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?

PatHartman

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.

military donut

ASKER

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.

PatHartman

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.

military donut

ASKER

OK, No problem

Thank you so much.
AMLD3055DB4-11---Copy.accdb

Jeffrey Coachman

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, ...so 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...?
...etc

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, ...it 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.

Thanks

JeffCoachman

military donut

ASKER

So, I did get what I was looking for...so 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 Coachman

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