Jump to content

MS Access Help


Recommended Posts

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 by Dahobbs
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Dahobbs
  • Like 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...