Joining 2 databases

[travisc]

VERY new to Access.



I've created relationships between my two databases (WorkCell and EventHistory), linking together Part#, ShiftDateTime, Machine#... which share the same column names and values.

Here's WorkCell:

ShiftDateTime	Machine #	Part #	Total Pieces
12/18/2015 05:45	Press # 01	Skin1	5
12/18/2015 05:45	Press # 01	Skin1	7
12/18/2015 05:45	Press # 02	Skin2	3
12/18/2015 05:45	Press # 02	Skin2	6

Here's EventHistory

ShiftDateTime	Machine #	Part #	EventCode	Defect Code
12/18/2015 05:45	Press # 01	Skin1	Scrap	Crack
12/18/2015 05:45	Press # 02	Skin2	Scrap	Blister
12/18/2015 05:45	Press # 02	Skin2	Scrap	Nonfill
12/18/2015 05:45	Press # 02	Skin2	Scrap	Blister

How could I create a query that would return:

ShiftDateTime	Machine #	Part #	Total Pieces	Crack	Blister	Nonfill
12/18/2015 05:45	Press # 01	Skin1	12	1	0	0
12/18/2015 05:45	Press # 02	Skin2	9	0	2	1

Is this possible? I current have a query with ShiftDateTime/Machine#/Part# linked together... grouping by shiftdatetime/machine#/part# then summing TotalPcs...

Any help would be greatly appreciated.

[ranman256]

2 things,

1. you say databases, but I think you mean 2 tables in 1 database. (database is the whole encompassing tables, queries, reports, etc)

2. yes you can join on part and date BUT, ShiftDateTime could be a problem because it uses time. IF ALL records use a permanent shift time of 5:45 then you will be OK.
If it is a date stamp of working on the machine then it will fail.

Join the 2 tables to get the fields you want and do a crosstab query to get your above results.

[June7]

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be MachineNum or Machine_Num.

I think you need to do aggregate query to first summarize the data of WorkCell table. Do a Sum([Total Pieces]) aggregate with grouping on Machine, DateTime, Part.

Build a CROSSTAB on the WorkHistory table with Machine, DateTime, Part as Row criteria. http://allenbrowne.com/ser-67.html

Now do a query that joins these two queries with compound link on the 3 key fields.