How to compile data from multiple tables and add new variable with a hard coded value

[jenandroy]

Hi, someone asked me to help them with their db for a weekly report and I have not worked in Access for over 20 years. Below is my question with example information and I'm hoping there is an easy solution.



Example Tables
Dog tbl
Cat tbl
Bird tbl
Fish tbl

Example Column fields in each example table: ID, Name, Date, Food

I want to compile all data from all tables (not loosing any records) and have new column field that tells me what table that data is from, such as "Type" with the values of “Dog”, “Cat”, “Bird”, “Fish” (not sure how I can hard code this). I would like to be able to export this data weekly into Excel for a pivot table report. Currently this person is exporting data from 12 separate tables weekly into excel and manually compiling all the data in Excel.

[Minty]

You'll need to insert the data one table at a time. Something like

INSERT INTO tb_CombinedData ( PetName, FeedingDate, Food, AnimalType )
SELECT [Name], [Date], Food, 'Dog'
From tbl_DOG

Change the 'Dog' to either the animal type for each table or better still have a table of animals and use the AnimalID Primary key field from the new table.

Notice I have used more descriptive sensible field names for the new table.
Date and Name are reserved names and/or functions in Access and using them as field names will cause you problems further down the line.

Date on its own means nothing - what date is it. Also your new autonumber ID Primary key field should be FeedingID or something else suitably descriptive not just ID.

[jenandroy]

thanks for responding. I do not know how to program behind the scenes in access. I can create a simple table or query using the available options in the "Create" section.

[Minty]

You can create a Insert query in the Query designer.
Do it a select query first to get your data correct then change it to a Insert query.

If you get stuck, either post a sample database here, or switch to SQL view in the query designer and copy and paste the SQL statement for us to look at, describing what it isn't doing for you.

[Gicu]

I don't think you need a new table, just use a union query:
"SELECT * FROM tbl_Dog, 'Dog' As Type
UNION ALL
Select * FROM tbl_Cat, 'Cat' As Type
UNION ALL
SELECT * FROM ...."

Cheers,