combining unrelated fields

[supind]

Hi. I hope someone can help me. I am trying to create a report that joins multiple unrelated fields into one report. I need to have these fields grouped under one main category.
TBL-Casting: fields Casting PN and Casting Description
TBL-CastProduction: fields CastingPN, Date, and Shots
TBL-FluorProduction: fields CastingPN, Date, RejQty, and TotalQty.

CastProduction and FluorProduction do not relate to each other in any way but both of these tables relate CastingPN to Casting PN in TBL-Casting.

I need for the fields to be available in a query without being combined by the Casting PN. Basically, what I need is to see every record based on a date or date range and in the report to have it grouped by Casting PN without having data used more than once.

If anyone could help me with this I would greatly appreciate it.
Thank you!

 

[GingerR]

Can you please provide sample data from each table, and an example of the output you want? Thanks!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[supind]

I will do my best to show how I need the data to be output.

Sample Data:

TBL-Casting:
Casting PN Casting Description
40123 Jeep Liberty
43783 Freshening
43925 Duraclad

TBL-CastProduction:
Casting PN Date Shots
40123 2/14/05 437
43925 2/14/05 465

TBL-FLuorProduction:
Casting PN Date RejQty
43783 2/14/05 12
40123 2/14/05 9

I need the query to see these as different entries rather than lumping them together under the same Casting PN. For example:
Date Casting PN Shots RejQty
2/14/05 40123 437
2/14/05 40123 9
2/14/05 43783 12
2/14/05 43925 465

Example of the output I am getting and dont want:
Date Casting PN Shots RejQty
2/14/05 40123 437 9
2/14/05 40123 437 9

Everything that I have come up with so far is putting the 40123's together etc and duplicating the data. There is an autonumber in the CastProduction and FLuorProduction tables but that just gets duplicated to fill in the spaces.

I apppreciate any help. Thank you very much!

 

[dhookom]

Try a union query.

SELECT [Casting PN], [Date], Shots, Null as RejQty
FROM TBL-CastProduction
UNION ALL
SELECT [Casting PN], [Date], Null, RejQty
FROM TBL-FluorProduction;

If your tables are similar, I would consider combining them into a single table with a field that identifies the "Cast" or "Fluor" value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[supind]

Thank you so much. That seems to have worked. I appreciate your help.