Bring Data From 3 Identical Backend Strctures Together

[fenhow]

Hello,

I have searched the forum and have found a lot of information however I am not sure just exactly what I am looking for.

Here is my situation.

It is a spilt DB.

I have a database with a form and a subform. The Primary Key in the form is unique to company A ie ID=CompanyA-1, CompanyA-2, and so on. There is a one to many relationship to the subform.

I have three of these databases in use, all identical execpt the Primary Keys. one is CompanyA, the others are CompanyB and CompanyC.

At the end of the month I want all companies, A,B,&C to send me their Backends so I can bring them all together into one database. The master database I want to bring all three backends into has the same Front End as the other three databases.

My question is what is the best way to bring all of the data together in one database so I can use the common front end to view it.

I am assuming that since the PK for each database is different they should all cometogether without much of a problem? If I can figure out how to merge them.

Thanks!

Fen How

 

[pbaldy]

Sounds like you'll want a UNION query to join the 3 into 1 temporarily. If you want a permanent solution, append queries to add the data from 2 into the 3rd, leaving you with 1 table with all records.

 

[fenhow]

Thanks Paul, I did happen to find some stuff on the append query. I do need a perm solution so that would be the way to go, I just tried to create one however when I try to run it I get an error "Duplicate Output Destination 'ID'

Any suggestions?

Fen

 

[fenhow]

When I do the append query and run the query it works great from design to run view howeve when I run it and it moves the data into the table i want to append to i lose my Primary Key Numbering convention.

I am using the following code in my PK Numbering

Autonumber, Long Increment, FORMAT: "Company1"&@@@

It will bring in the number but not the format which is key in identifying the different companies.

How can I get it to remain the same as the source table?

Fen

 

[pbaldy]

I thought they already had a unique key?

The Primary Key in the form is unique to company A ie ID=CompanyA-1, CompanyA-2, and so on.

 

[fenhow]

They do, you are correct. When I append them to the New Table which has nothing (no Primary Key)in it before the append, it only takes the number from the table I am appending not everything such as the "CompanyA"

When they are all appended I have to see by every record which company did it that is why I have the following format "Company1"&@@@ to my Primary Key in the three databases the other companies use.

Thanks Paul.

Fen

 

[Daveyk01]

Hello Fen How,

I do something similar. I take my database mobile and make changes, record additions, etc in the field. When I get back to home base I want to upload the field created records to the main database.

Here is a code I use in one of my simpler databases to do that. You of course would need to change it to suit your needs, but the idea is there:

Private Sub XFERFieldRecords_Click()
Dim strSQL1 As String
Dim rs1 As Recordset, rs2 As Recordset
Dim db1 As Database, db2 As Database
Dim Fld As Field
Dim status As Variant
Dim X As Integer, Y As Integer, NothingToUpload As Integer
'
' rs1 = Mobile table
' rs2 = Network tables
'
status = MsgBox("Use this funtion with extreme caution! " & Chr(13) & Chr(13) & "Note: At this point, you MUST be linked for Mobile Operations!" & Chr(13) & Chr(13) & "Do you wish to continue?", 52, "TRANSFER FIELD REPORTS")
If status = vbNo Then Exit Sub
'
' Check for ModuleLists to upload
Set db1 = CurrentDb
'
' Open the network PhasorXS_QCP back-end
'
Set db2 = DBEngine.Workspaces(0).OpenDatabase(InHousePath)
'
'
For z = 1 To 7
Select Case z
Case 1
Set rs2 = db2.OpenRecordset("ModuleList", dbOpenDynaset)
'Open the mobile modules table for any records marked for upload
strSQL1 = "SELECT * FROM [ModuleList] WHERE RecordedInField = TRUE ORDER BY TESTDATE;"
strTableName = "Modules List Records"
Case 2
Set rs2 = db2.OpenRecordset("AttenComp", dbOpenDynaset)
'Open the mobile AttComp table for any records marked for upload
strSQL1 = "SELECT * FROM [AttenComp] WHERE RecordedInField = TRUE ORDER BY LINBOXSN;"
strTableName = "Attenuator Compensation Records"
Case 3
Set rs2 = db2.OpenRecordset("CVTestResults", dbOpenDynaset)
strSQL1 = "SELECT * FROM [CVTestResults] WHERE RecordedInField = TRUE ORDER BY TESTDATE;"
strTableName = "Conventional Test Results Records"
Case 4
Set rs2 = db2.OpenRecordset("ElementReadings", dbOpenDynaset)
strSQL1 = "SELECT * FROM [ElementReadings] WHERE RecordedInField = TRUE ORDER BY TESTDATE;"
strTableName = "Histogram Data Points"
Case 5
Set rs2 = db2.OpenRecordset("PALinearity", dbOpenDynaset)
strSQL1 = "SELECT * FROM [PALinearity] WHERE RecordedInField = TRUE ORDER BY TESTDATE;"
strTableName = "Phased Array Linearity Records"
Case 6
Set rs2 = db2.OpenRecordset("PAProbes", dbOpenDynaset)
strSQL1 = "SELECT * FROM [PAProbes] WHERE RecordedInField = TRUE ORDER BY [PROBE SN];"
strTableName = "Phased Array Probe Serial Number Records"
Case 7
Set rs2 = db2.OpenRecordset("PATestResults", dbOpenDynaset)
strSQL1 = "SELECT * FROM [PATestResults] WHERE RecordedInField = TRUE ORDER BY TESTDATE;"
strTableName = "Phased Array Test Result Records"
End Select
'
Set rs1 = db1.OpenRecordset(strSQL1, dbOpenDynaset)
'
If rs1.RecordCount > 0 Then
rs1.MoveLast
rs1.MoveFirst
MsgBox Str(rs1.RecordCount) + " " + strTableName + " to upload", vbOKOnly
NothingToUpload = False
ElseIf rs1.RecordCount = 0 Then
MsgBox "No " + strTableName + " to upload", vbOKOnly
NothingToUpload = True
End If
'
If Not NothingToUpload Then
For X = 1 To rs1.RecordCount
'
rs2.AddNew ' Add new record to network database
'
Y = -1 ' set counter to "0" as the fields array starts at "0"
'
For Each Fld In rs1.Fields
'
Y = Y + 1 ' incriment Fields array pointer
'
If Y <> 0 Then
If Not IsNull(rs1.Fields(Y)) Then
rs2.Fields(Y) = rs1.Fields(Y)
End If
Else
rs2.Fields("RecordedInField") = False
End If
'
Next Fld
'
rs2.Update ' update record in Network Database
'
rs1.MoveNext ' move to next record in db (0)
'
Next X
End If
' Close out the tabels (recoredsets)
rs2.Close
Set rs2 = Nothing
rs1.Close
Set rs1 = Nothing
'
Next z
'
' Close the databases before exiting!!!
db1.Close
Set db1 = Nothing
db2.Close
Set db2 = Nothing
End Sub