Inserting name of query as a field 2

[postlethwaited]

Hi all, is it possible to insert text cut from a table name into a field in a query - to identify the source of the data in it?

 

[AccessGuruCarl]

Could you explain a little more in detail, what you are attemping to do?

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[postlethwaited]

of course, basically i have 6 tables that i want to pull two fields from and append all the data into one large table. I want to identify which underlying table each line of data came from - so i would like to put the original table name as a field in the new table - I hope this is clearer, thanks!

 

[AccessGuruCarl]

Is the a one time deal, or do you want to be able to run this from code?

I'll post some samples tommarrow based on your reply if you need code examples.

What is the structure(fields) of the tables.
Is this the new table?
OrgTableName - Field1Data - Field2Data

But if you want to play around, make an append query to append the data from table 1, make an update query to update tablename where table name = ""

Next change the first query or make a new one to select table2, run that, then run the update query again changing the table name, or make a new one.

If you need to run this more than once, create new queries! You can then call these from VBA.

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[postlethwaited]

appreciate it Carl - I will be looking to run the append query each day - i will have a play with the code and look forward to your post tommorrow! thanks again.

 

[PHV]

a starting point (SQL code):
INSERT INTO newTable SELECT * FROM (
SELECT 'Table1' AS F1, someField, anotherField FROM Table1
UNION SELECT 'Table2', someField, anotherField FROM Table2
UNION SELECT 'Table3', someField, anotherField FROM Table3
UNION SELECT 'Table4', someField, anotherField FROM Table4
UNION SELECT 'Table5', someField, anotherField FROM Table5
UNION SELECT 'Table6', someField, anotherField FROM Table6
) AS U

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AccessGuruCarl]

How did make out with queries...

If they are working, and you want to keep the queries create a command button to run these in order.

Private Sub Command0_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1Append"
DoCmd.OpenQuery "Query1Update"
DoCmd.OpenQuery "Query2Append"
DoCmd.OpenQuery "Query2Update"
DoCmd.OpenQuery "Query3Append"
DoCmd.OpenQuery "Query3Update"
...
DoCmd.SetWarnings True
End Sub

You need to change the update query to read Is Null in the where clause instead of ""

If you prefer VBA you can try this,

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO newTable ( field1, field2 )"
strSQL = strSQL & "SELECT somefield1, somefield2 FROM Table1"
DoCmd.RunSQL strSQL
strSQL = "UPDATE newTable SET tablename = '" & "Table1" & "'"
strSQL = strSQL & " WHERE tablename Is Null"
Debug.Print strSQL
DoCmd.RunSQL strSQL
'...
'Copy and paste and rename fields as needed...
'...
DoCmd.SetWarnings True

Hope this helps....
Carl


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[postlethwaited]

thanks to all for the help here - i really appreciate it