how can we do the following . Thanks for ur help

[deepakes2]

Hello : Some one please help me get this output.

Table 1 :

Username location gender


Table 2:



Username location gender age

Table 3:

Username location gender ID


output

Table1 username location gender
Table2 username location gender age
Table3 username location gender __ ID

Plz let me know if you have any questions. Please help me get a similar output. I have the data in different tables in access DB . i have to merge the tables into one table.

[andy49]

Each table has how many fields? Its unclear from your description

Tbl1
username
location
Gender

Are the usernames the same data?

[deepakes2]

Each table has more than 30 fields which are common and there are few fields ( 2 or 3 ) randomly added to few tables. I need to keep all the records . there may be duplicated usernames.

[pbaldy]

How about

SELECT username, location, gender, Null As Age, Null As ID
FROM Table1
UNION ALL
SELECT username, location, gender, Age, Null As ID
FROM Table2
UNION ALL
SELECT username, location, gender, Null As Age, ID
FROM Table3

[orange]

deepakes,

What is your situation exactly? Are you designing a database?
Modifying an existing database?

Perhaps you could describe what you are trying to accomplish in very simple English.
Without some context we can only guess and provide very general suggestions.

[ssanfu]

@deepakes,
And please pick one thread or the other - you have 2 threads going that are the same question.
https://www.accessforums.net/showthread.php?t=64348
https://www.accessforums.net/showthread.php?t=63148

[deepakes2]

I have tables in the access DB . my task is to merge all the tables into one table while indexing the data to know the source table. Each table has around 30 identical fields. some of the tables have additional fields (no specific pattern). Lets not worry about the added fields and just focus on the identical fields.
I have numerous tables in the DB and I have to perform this on numerous access DB. So i am looking to get some help to create a loop or something .

Something in the lines of

n automated way would be something like:

For i = 1 to 31Docmd.RunSQL "INSERT INTO tbl2015Jan ( ColumnA, ColumnB, ColumnC, TableName )
SELECT tblJan(i).ColumnA, tblJan(i).ColumnB, tblJan(i).ColumnC, "tblJan(i)"
FROM tblJan(i)"
next i

plz advice. Thanks for looking

[orange]

To get focused advice, you are going to have to do much better than
"So i am looking to get some help to create a loop or something" .

Tell us about the tables. Their design. Why do you have so many tables?
What is the background/source of these tables?

Post a copy of a couple of tables to highlight the issue/opportunity. It would be helpful to you and readers if you showed us the inputs and a sample of the output you need.
Good luck

[pbaldy]

It would look like:

"...FROM tblJan" & i

Since there's only one, you don't need to specify the table in the SELECT clause, only the FROM clause.

[deepakes2]

Sorry if i am not able to give all the specific details in the previous posts . I will try to explain best i can . I have various access data bases with around 50 or more tables generated daily basis and sometimes multiple times a day. each data base is created every month. I have multiple access db with tables. each table contains around 30 fields(common fields). My task is to merge all the tables into one table with index to track the data source.I have to create one big table in each access db i have. Please let me know if you have any questions. I am not into programming . i don't have lot of experience with access . Please help me with some kind of a code which can loop and create one big table with index(can be the table name). thanks for looking. I appreciate any help towards this.