MS Access Help

[Longhorn Fever 275]

Can any of you wizards explain how to combine data from 30+ tables into one new table table with a new column that adds the original table name from which each row of data was extracted?

[Dahobbs 18318]

2 hours ago, Longhorn Fever said:

Can any of you wizards explain how to combine data from 30+ tables into one new table table with a new column that adds the original table name from which each row of data was extracted?

Are these items related? Or are they all independent? If the former, google excel Power Query/Get Data. It is a feature in Excel that'll let you combine related data based upon a common identifier. You can also add new columns with whatever other information you want (and it can be conditioned). If the latter, copy and paste?

Just realized you said Access. You can do some of the same stuff in that as you can in Power Query. But I don't use it enough to know the commands off the top of my head. Why do you want to combine them? Generally that is less necessary in Access and other full fledged database tools. 

Edited April 13, 2020 by Dahobbs

[Dahobbs 18318]

6 hours ago, Longhorn Fever said:

Can any of you wizards explain how to combine data from 30+ tables into one new table table with a new column that adds the original table name from which each row of data was extracted?

I guess I should also ask the format of the underlying database. Is it an Access Relational Database or just an Access front end? 

[You don't know me 1163]

I would extract each table to a spreadsheet in Excel, add the column with the table name, then paste them into one spreadsheet.  After that, just import into a new table.

[You don't know me 1163]

Disclaimer - my response is the way it is because I'm not an expert at Access.  I'm a MySQL guy, but I can normally fiddle my way through any sort of database.  However, the time you spend researching and figuring out how to automate it may be shorter than just doing it the way I said.

Actually, I thought of another option.  If all of the tables are identical, you could create a query that includes the data from all of the tables using a UNION ALL between (if Access even recognizes UNION ALL).

I'd be glad to help.  I have Access on my machine if you want to send the database to me.  PM me if you want me to take a look.

[markstanco 12786]

Unrelated to the issue, i didn't know access was still a thing.

[Nice Guy Eddie 13729]

If this is a one time need, just build queries to move over each table data and type in the table name as an extra field/value to append to the new column in the combined table. Yeah, it’s a pain but you’ll be done in 30 minutes.

if it’s a continual need, then save each queries and add them to a macro.

the main risk with is missing a table or appending one table twice.

[Angry Gorilla 1771]

1 hour ago, Nice Guy Eddie said:

If this is a one time need, just build queries to move over each table data and type in the table name as an extra field/value to append to the new column in the combined table. Yeah, it’s a pain but you’ll be done in 30 minutes.

if it’s a continual need, then save each queries and add them to a macro.

the main risk with is missing a table or appending one table twice.

This is how I would do it

[Dahobbs 18318]

10 hours ago, markstanco said:

Unrelated to the issue, i didn't know access was still a thing.

Access is used pretty frequently as a GUI front-end for other types of databases. Using Access as the back-end for a database is probably only done by businesses that just need something a tad more formal than a spreadsheet. 

Edited April 14, 2020 by Dahobbs

[Dahobbs 18318]

1 hour ago, Nice Guy Eddie said:

If this is a one time need, just build queries to move over each table data and type in the table name as an extra field/value to append to the new column in the combined table. Yeah, it’s a pain but you’ll be done in 30 minutes.

if it’s a continual need, then save each queries and add them to a macro.

the main risk with is missing a table or appending one table twice.

I gather that building queries is probably the part he needs help with. 

[Nice Guy Eddie 13729]

11 hours ago, markstanco said:

Unrelated to the issue, i didn't know access was still a thing.

I would be hesitant to use Access to build a new application today but it's a great analytical tool to use alongside Excel.