Multiple Queries with Multiple Fields

[Guest]

I need to create a report from 5 different queries that have different amount
of fields.

qrMSM(field1, field2, field3, field4, field5, field6)
qrBfill(field1, field2)
qrQfill(field1, field2)
qrPtfill(field1, field2)
qrUphfill(field1, field2)

how can i create a union query that will give me a report from all these
queries?

 

[John Spencer]

Assuming that field1 and Field2 all align then the following

SELECT Field1, Field2, Field3, Field4, Field5, Field6
FROM qrMSM
UNION ALL
SELECT field1, Field2, Null, Null, Null, Null
FROM qrBFill
UNION ALL
....

Just pad the select statements with Null for the fields that are missing.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Yes, that seems to work perfectly, however, i guess i didnt explain myself
further.

SELECT ModelNumber, Border, Spring, Size, FoamCore, FoamEnc, Pattern FROM
qrMSM
each feild in this query displays only one record per Model Number and in
the query bellow

SELECT ModelNumber, Bfill FROM qrBFill
Bfill might have 3 records per ModelNumber.

how can i put these two queries together to create an report that will
display whatever is in qrMSM AND qrBfill....thanks for your help

 

[John Spencer]

The answer depends on what you want to do in the report and how the various
queries are related.

For instance, you could use one report based on qrMSM and sub-reports based
on each of the other queries that are linked to the main report.

Or you could relate the main query to each subquery.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Well, the queries have the same ModelNumber and what i want to do in the
report is to display one ModelNumber, that have a Border, Spring, Size,
FoamCore, FoamEnc, Pattern (all from one query qrMSM) and couple of Quilts
fills(qrQfills) and Bottom Fills(qrBfill), Uph Fills(qrUphfill) and Pt Fills
(qrPtfill)which each come from different Queries. I LIKE TO LIST THEM IN THE
REPORT.

Does that explains a little of what i want to do?

 

[John Spencer]

Not very well.

SELECT DISTINCT qrMSM.ModelNumber, Border, Spring, Size,
FoamCore, FoamEnc, Pattern
, Bfill
, qFill
FROM (qrMSM LEFT JOIN qrBFill
ON qrMSM.ModelNumber= qrBFill.ModelNumber)
LEFT JOIN qrQfills
ON qrMSM.ModelNumber= qrQFills.ModelNumber

That will give you multiple lines with of data with one line for each
combination of of qrMSM, qrBfill, and qrQFills. I'm don't think that is
what you want.

The solution you may need would be to build a report for each of the queries
you have.
Select the qrMSM as the master report and then include each of the other
reports as subreports on the master report.

An alternative might be to use Duane Hookom's concatenate function to build
a comma delimited list of of items based on each of your other queries.
After downloading his code and pasting it into a module you would build the
lists Code is at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

SQL might look something like
SELECT qrMSM.ModelNumber, Border, Spring, Size,
FoamCore, FoamEnc, Pattern
, Concatenate( "SELECT Bfill FROM qrBFill WHERE ModelNumber = """ &
qrMSM.ModelNumber & """") as BFILL
, Concatenate("SELECT UFill FROM qrUphFill WHERE ModelNumber = """ &
qrMSM.ModelNumber & """") as UFILL
FROM qrMSM


John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Man this is all beginning to clear up for me...and i appreciate
it.....now...this is the SQL that i have, but i get a undefined function
error.

SELECT qrMSM.ModelNumber, qrMSM.Border, qrMSM.Config, qrMSM.FoamCore,
qrMSM.FoamEnc, qrMSM.Label, qrMSM.Needle, qrMSM.Size, qrMSM.Pattern,
Concatenate("SELECT Bfill FROM qrBfill WHERE ModelNumber = """ &
[qrMSM].[ModelNumber] & """") AS Bfill, Concatenate("SELECT Qfill FROM Qfills
WHERE ModelNumber = """ & [qrMSM].[ModelNumber] & """") AS Qfill
FROM qrMSM;

and i did wat you suggested me to do as an alternative...i copied the
Function from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
into an Module in my database and called it Concatenate............didnt
touch anything, just copied it.......now, when i try to run the SQL query
above, it says undefined function. ANY BUG.......that i might not be
seeing......HELP......thanks....
i dont know if the ModelNumber quotes might be the problem.....note that
modelNumber is a string....just in case is defined in this sql statement as
for a Int or any other Number type

 

[Guest]

can you please explain your other suggestion: creating a master report and
child reports....how do i go about this?

 

[John Spencer]

DId you name the module something other than the name of the function.
Name the module something like modConcatenate.

THe quotes are correct if modelNumber is a text field.

Perhaps someone else ca help you



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Hey John......
i got it working....thanks man...you the best..