Many to Many Relationship

[hikerdood]

I am tasked with tracking issued labor hours per job order along with expended hours. The database that I extract the Job Order info has the following headers but not dates:

COST FY

TOPD

JO#

JO Title KE# KETitle

Issued Hours

FY-19 SFSI 123456 Hand Fnsh 002 CMPL 640


FY-19 MYFF 123456 Sanding 001 HFSG 1500

The database that I extract the expended hours from has the following headers along with dates

COST FY

TOPD

JO#

KE#

DDDATE

DDMonth

Expended Hrs

FY-19 SFS1 123456 002 3-1-19 March 54
FY-19 SFS1 123456 002 3-2-19 March 120
FY-19 SFS1 123456 002 3-5-19 March 85
FY-19 MYFF 123456 001 3-4-19 March 250
FY-19 MYFF 123456 001 3-6-19 March 430

It doesn't seem possible to do a join such that I get issued hours and then the dates and months along with expended hours. This is what I get when I create the relationship extracting the fields that I want.

COST FY TOPD JO# JO Title KE# KETitle MONTH Issued Hours Expended Hours
FY-19 SFS1 123456 Hand Fnsh 002 CMPL March 640 54
FY-19 SFS1 123456 Hand Fnsh 002 CMPL March 640 120

I get a repeat of the issued hours for each record that I pull from the Labor file. I understand this as it is a many to many relationship and I can't use a Primary Key because the JO repeats it self in both files. Is there anyway to avoid the repeat of the issued hours. I need to export this file to excel and sum up the expended hours against the total issue hours per job which on this JO is only 640 and not 1280. Any help is appreciated


Regards

Gene

[CJ_London]

suggest you need an aggregate query for each table, grouping on SFS1 123456 002 and summing expended hours in one query and issued hours in the other

then join the two queries together in another query, joining on SFS1 123456 002

I've used values because your tables don't line up so can't work out what's what - perhaps

TOPD

JO#

KE#

?

[hikerdood]

Thanks AJAX. Sorry but I forgot to mention that the expended hours of the 640 that are issued can also run into April and May. If I sum the expended hours I will get the Months March, April and May. Will this not carry the 640 into the months of April and May also?

Regards

Gene

[CJ_London]

your data is not clear since you are now expanding on what the data actually is, nor is the outcome you require. Since one of your tables does not have dates how do you propose to specify which month or months it should apply to?

[hikerdood]

I agree it does not seem possible. The Job Order file that has the issued hours has no dates,however the month that it should apply to would be the very first expended labor hour date from the labor file. This is when the Job started. I am trying to have an outcome such that the Job was issued 640 hours and our first expended hours from the labor file had a date of March. An indication to me when the Job actually started and then for that same job we expended 125 of hours in April and 250 hours hours in May. The end result would tell me the true labor hour cost of that job over the 3 months.We issued 640 hours, however cost to complete was only 590 hours over those 3 months "good for us" or 750 hours "bad for us" and we need to do some better time studies. I was thinking about creating 12 monthly queries and tying them together but I would still get the same repeat of issued hours. I am brain storming on how to not repeat the 640 hours which I think would require having the months go across the table horizontally instead of down vertically. We have thousands of Job Orders going through the plant and the Labor file "using the transaction date" can have 32000 records. The labor file record size is reduced significantly when aggregated by the month.

Thanks for your response.

Regards

Gene

[CJ_London]

rather than describe the problem, provide some example data and what outcome you require from that example data. The data needs to cover all eventualities which your initial post does not.

[hikerdood]

Cost FY TOPD JO# JOTITLE KE# KETITLE ISSUED Hrs Exp Hrs March Exp Hrs April Exp Hrs May Total Exp Hrs
FY-19 SFS1 123456 HNDFNS 002 CMPLT 640 275 245 244 764
FY-19 MIFI 768910 RFFNS 001 STRL 765 324 262 50 636
FY-19 QBGE 345679 PLLY 004 FGRY 234 78 125 31 234

I would have the 12 months of the year listed across the top. I could then create an alias column showing the difference between issued and actual. Or, I could pull this down into excel. I hope this posts correctly and aligns up right. I don't know how to attached an excel file if it is possible to give you a better example.

Appreciate your repsonses
Gene

[CJ_London]

sorry, not making sense to me (you know your data, we don't), perhaps attach an excel file so columns are lined up. Also this does not look like your original data and no idea what the values are supposed to represent. You need to be much clearer

[Micron]

you can also enter all into Excel then copy then paste here. You'll get html table. Suggest you center align text before copying. "Go Advanced" here to adjust table properties.

[hikerdood]

Hope this works. I uploaded a zipped Test Excel file with a few records showing the required outcome.

Reg

[hikerdood]

costfy	topd	jo#	jotitle	ke#	ketitle	Iss Hrs	exp mar	exp apr	exp may	total exp	over/under
fy-19	sfs1	123456	hndfnsh	002	cmplt	640	125	240	285	650	-10
fy-19	myff	123457	rffnsh	003	ppll	765	185	175	265	625	140
fy-19	jpek	123458	clcr	004	dfup	234	78	102	54	234	0

[CJ_London]

@hiker. I don't think your are getting it. I asked for examples of your source data (2 tables) which covers all the possibilities, plus the outcome you expect from that example data - the data provide in your first post was clearly insufficient because of your comments in post #3. Here we are 8 posts later and no further forward. If you are unable to do this, then I regret I will have to drop out.

[hikerdood]

Job Order File
COSTFY	TOPD	JO#	JOTITLE	KE#	KETITLE	Issue
FY-19	SFS1	5ZL50330	MFR	002	CHILL	240
FY-19	PJK3	B7501002	PATT	003	BRK	225
Labor File
COST FY	TOPD	JO#	Trans Date	DD Month	Expended Hrs
FY-19	SFS1	5ZL50330	3-2-19	March	56
FY-19	SFS1	5ZL50330	3-4-19	March	54
FY-19	SFS1	5ZL50330	3-6-19	March	47
FY-19	SFS1	5ZL50330	3-8-19	March	32
FY-19	SFS1	5ZL50330	3-12-19	March	85
FY-19	PJK3	B7501002	4-2-19	April	36
FY-19	PJK3	B7501002	4-5-19	April	45
FY-19	PJK3	B7501002	4-6-19	April	22
FY-19	PJK3	B7501002	4-8-19	April	45
FY-19	PJK3	B7501002	4-12-19	April	54
Outcome
COST FY	TOPD	JO#	JOTITLE	KE#	KETITLE	Issue	Exp March	Exp April	Total Exp
FY-19	SFS1	5ZL50330	MFR	002	CHILL	240	274	0	274
FY-19	PJK3	B7501002	PATT	003	BRK	225	0	202	202

[CJ_London]

OK - makes more sense now. I assume 'FY-19' means 'Financial Year 2019' this may need to be modified to account for when that ends - I presume you can have jobs created in one FY and expended in the next one. I've also based the month on the transaction date since there is otherwise no way of telling whether 'March' applies to this year or another year. And since you require a non sortable order for the columns (Jan comes after Feb, Mar comes after April, etc) it is necessary to specify the column headings in the PIVOT part of the sql.

TRANSFORM Sum(tbLabor.ExpendedHrs) AS SumOfExpendedHrs
SELECT tblJobOrders.CostFY, tblJobOrders.TOPD, tblJobOrders.JO, tblJobOrders.JOTitle, tblJobOrders.KE, tblJobOrders.KETitle, tblJobOrders.Issue, Sum(tbLabor.ExpendedHrs) AS SumOfExpendedHrs1
FROM tblJobOrders LEFT JOIN tbLabor ON (tblJobOrders.JO = tbLabor.JO) AND (tblJobOrders.TOPD = tbLabor.TOPD)
GROUP BY tblJobOrders.CostFY, tblJobOrders.TOPD, tblJobOrders.JO, tblJobOrders.JOTitle, tblJobOrders.KE, tblJobOrders.KETitle, tblJobOrders.Issue
PIVOT "Exp " & Format([transdate],"mmmm") In ("Exp March","Exp April");

To show all 12 months of a year, you will need to extend the pivot to include the extra months - ("Exp March","Exp April","Exp May"...) etc. Months without data will show as a blank column

[hikerdood]

Alax, Apologize for not nailing what you wanted the first time. Absolutely appreciate your patience with me.

Yes our projects from start to completion take 5 years. FY-19 equals Fiscal Year 2019. Job specific orders aka "sub task's" issued hours can run for multiple months and extend in a new fiscal year. Approx number of job orders per project can range from 500-1000. When you talk about Pivot table are you thinking Access Pivot table created in excel once results are pulled down into excel or a Pivot Table created in Access.

Again thanks for your help. Would you mind if I have some issues to re-visit.

Regards

Gene