join two tables

[Magna]

Hello All and thanks for looking. ok what I have is an access database with two tables in it one table is old data and the other one is new data that can be refreshed daily I want to make a table that will join the two tables together, so I can have a full YTD. thanks again

 

[Joe4]

Two join all the data from two like tables together in a single query, you would use a UNION query (see: https://support.office.com/en-za/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05).

Alternatively, you could import the records from table into the other using an Append Query. Of course, then you have two copies of certain records in two different tables (unless you were to then delete the original).

 

[Magna]

thanks for you link and I got it to work... kinda it will combine the two tables but about half of the cells turn into a count and not the number.. and ideas?

Two join all the data from two like tables together in a single query, you would use a UNION query (see: https://support.office.com/en-za/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05).

Alternatively, you could import the records from table into the other using an Append Query. Of course, then you have two copies of certain records in two different tables (unless you were to then delete the original).

 

[Joe4]

thanks for you link and I got it to work... kinda it will combine the two tables but about half of the cells turn into a count and not the number.. and ideas?

Post the SQL code of your query, and let us know which fields you are referring to.

 

[Magna]

Post the SQL code of your query, and let us know which fields you are referring to.

SELECT [Production Reporting Archive 2015 Q1Q2].ID, [Production Reporting Archive 2015 Q1Q2].Resource, [Production Reporting Archive 2015 Q1Q2].[Part #], [Production Reporting Archive 2015 Q1Q2].[Crew Size], [Production Reporting Archive 2015 Q1Q2].Operator, [Production Reporting Archive 2015 Q1Q2].Date, [Production Reporting Archive 2015 Q1Q2].[Shift #], [Production Reporting Archive 2015 Q1Q2].[Total Pcs Produced], [Production Reporting Archive 2015 Q1Q2].[Scrap/Reject], [Production Reporting Archive 2015 Q1Q2].[Total Good Parts], [Production Reporting Archive 2015 Q1Q2].Notes, [Production Reporting Archive 2015 Q1Q2].Scrap, [Production Reporting Archive 2015 Q1Q2].[FTQ (%)], [Production Reporting Archive 2015 Q1Q2].[Uptime (%)], [Production Reporting Archive 2015 Q1Q2].[Hourly Standard], [Production Reporting Archive 2015 Q1Q2].[OA/OEE], [Production Reporting Archive 2015 Q1Q2].TotalUptimeNum, [Production Reporting Archive 2015 Q1Q2].ScheduledTimeNum, [Production Reporting Archive 2015 Q1Q2].[CT (%)], [Production Reporting Archive 2015 Q1Q2].Building
FROM [Production Reporting Archive 2015 Q1Q2]
UNION ALL SELECT [Production Entry].ID, [Production Entry].Resource, [Production Entry].[Part #], [Production Entry].[Crew Size], [Production Entry].Operator, [Production Entry].Date, [Production Entry].[Shift #], [Production Entry].[Total Pcs Produced], [Production Entry].[Scrap/Reject], [Production Entry].[Total Good Parts], [Production Entry].Notes, [Production Entry].Scrap, [Production Entry].[FTQ (%)], [Production Entry].[Uptime (%)], [Production Entry].[Hourly Standard], [Production Entry].[OA/OEE], [Production Entry].TotalUptimeNum, [Production Entry].ScheduledTimeNum, [Production Entry].[CT (%)], [Production Entry].Building
FROM [Production Entry];

 

[Magna]

Resource, Part #, Operator
I think just the 3

 

[Joe4]

So which fields are not behaving in the manner you expect them to?
What do some of their values look like before the UNION Query, and what do they look like afterwards?

 

[Magna]

Resource is a machine
1501
1501
1501
and so on now its a count
3
3
3
when I 1st run it, it works fine it does not change until I save it close out and then re-open it

 

[Joe4]

I am afraid I am not understanding what you are telling me. You don't appear to have any Aggregate Functions at work here.
Without seeing your source data of each table, I am not sure I can tell what is happening.

 

[Micron]

Are you dumping the query results into a table that has calculated fields (possible in Access 2010 and later, I believe)? If not, where are you "saving" it?