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

 

[Magna]

I am pulling the two main tables from share point, and I want to combine 1st & 2nd Quarter with all current info so I can get a YTD table
here is some data maybe it will help show what is going on
data before

ID Resource Part # Crew Size Operator Date Shift # Total Pcs Produced Scrap/Reject Total Good Parts Notes Scrap FTQ (%)
30 1501 AT524A 3 XXXXXXXXXX 01226 3/3/2015 2 2000 25 1,975.00 Power outage 1.25% 98.75%
31 MUR#4APZ JA223C 1 XXXXXXXXXX 00856 3/3/2015 2 600 30 570 Meetings 1 hour 5.00% 95.00%
32 MUR#4 JA262AB 1 XXXXXXXXXX 01221 3/3/2015 1 644 8 636 No issues 1.24% 98.76%
33 MUR#7 JA262C 1 3/3/2015 2 1000 1 5 985 SL 1.50% 98.50%
34 1501 AT524A 2 XXXXXXXXXX 00335 3/4/2015 3 2000 100 1,900.00 meetings again 5.00% 95.00%
36 MAZ#3 JA222D 3/3/2015 1 322 2 320 0.62% 99.38%
40 KIT#4 JA222AC 3/3/2015 1 606 44 562 NCU Parts 7.26% 92.74%
41 MUR#1 JA225A 3/3/2015 1 664 29 635 NCU Parts 4.37% 95.63%
42 MUR#2 JA225A 3/3/2015 1 449 4 445 0.89% 99.11%

data after
ID Resource Part # Crew Size Operator Date Shift # Total Pcs Produced Scrap/Reject Total Good Parts Notes Scrap FTQ (%)
30 165 12 3 173 3/3/15 9:41 AM 2 2000 25 1975 Power outage 0.0125 0.9875
31 282 193 1 60 3/3/15 1:15 PM 2 600 30 570 Meetings 1 hour 0.05 0.95
32 281 179 1 170 3/3/15 1:32 PM 1 644 8 636 No issues 0.01242236 0.98757764
33 287 189 1 3/3/15 1:46 PM 2 1000 15 985 SL 0.015 0.985
34 165 12 2 17 3/4/15 9:23 AM 3 2000 100 1900 meetings again 0.05 0.95
36 266 168 3/3/15 6:07 PM 1 322 2 320 0.00621118 0.99378882
40 260 164 3/3/15 6:43 PM 1 606 44 562 NCU Parts 0.072607261 0.927392739
41 267 165 3/3/15 6:45 PM 1 664 29 635 NCU Parts 0.043674699 0.956325301
42 274 176 3/3/15 6:48 PM 1 449 4 445 0.008908686 0.991091314
43 271 166 3/4/15 6:53 PM 1 664 29 635 NCU issue 0.043674699 0.956325301
44 285 178 3/3/15 6:57 PM 1 459 7 452 0.015250545 0.984749455
46 258 175 1 366 3/4/15 10:57 AM 2 500 0 500 0 1
47 279 177 1 198 3/4/15 12:41 PM 1 527 9 518 0.017077799 0.982922201
48 285 178 1 366 3/4/15 12:43 PM 2 492 6 486 0.012195122 0.987804878
49 260 164 1 171 3/4/15 12:45 PM 2 798 5 793 0.006265664 0.993734336
50 271 166 1 171 3/4/15 12:47 PM 2 928 4 924 0.004310345 0.995689655

 

[Joe4]

So after you do the Union Query, are you then trying to do an Aggregate (Totals) Query to combine records (to count or sum up values)?
If so, then you just need to be sure that you are Grouping By the right fields, and choosing the correct Aggregate Function for the other fields (COUNT, SUM, etc).

 

[Magna]

after the union query I don't even know how to change the aggregates its all in it. its in the SQL view, if I run one table by it self it works fine, its only when I do the union that it changes. I know I am just over looking something simple but I am at a lost on what to do at this point...

 

[Joe4]

It is pretty hard to try to figure out what might be going on without having access to the data tables.
The only other thing I can think of is to check to make sure all corresponding fields between the two tables are exactly the same Data Types.

 

[Magna]

the tables I'm working with are linked to share point and the table its self when has some columns listed as numbers when they need to be text and I thin this could be one issue

 

[Joe4]

Yes, the corresponding fields from each table need to be the same Data Type, or else you may see some odd results.
You may need to create a query to convert certain fields to text or numbers, and then use those query in the Union query to get what you want.

 

[Magna]

Yes, the corresponding fields from each table need to be the same Data Type, or else you may see some odd results.
You may need to create a query to convert certain fields to text or numbers, and then use those query in the Union query to get what you want.

now if I can just fig out how to do the conversion...
ResourceText:Cstr([Resource])
looking around I found this and put it in the field cell... and I cant seem to get it to work

 

[Joe4]

I cant seem to get it to work

How so? Are you getting some sort of error?
What do these values look like?
Do you have any "error" values in your "Resource" field before applying the function?

 

[Magna]

invalid use of null

 

[Joe4]

Sounds like you may have some Null values in your data. You may be able to make use of the NZ function to handle those, i.e.

[COLOR=#333333]ResourceText:Cstr(Nz([Resource],""))[/COLOR]