query to join multiple queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need help with this,

This is a query that I'm using to consolidate other queries that perform the
same calculations, only for different date periods.

SELECT [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF
FROM ((((1stPeriod INNER JOIN 2ndPeriod ON [1stPeriod].FltNumOrgDst =
[2ndPeriod].FltNumOrgDst
GROUP BY [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF;

The problem I have with it, is that if a FltNumOrgDst doesn't appear in all
the other queries, it eliminates from the consolidating query. It doesn't
return blank or null values I think.

I need it to return every single value for FltNumOrgDst, even if it doesn't
appear in a specific period.

thank you for your help
 
Your query employs (in technical jargon) an "equi-join", which only shows
rows when ALL joined recordsets have matching join-field values.

One way to approach your situation is to first create/use a query that lists
all possible ...?district orgs? ..., then join that recordset to each of
your "period" datasets with a directional join. First drag your ID from
your "all" list to your period list matching field, then right-click on the
join line and change the join properties to "all of one list and any that
match from the other". Repeat for each additional "period" dataset.

This will display every ... ?district org? ... and any matching value from
your "period" queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff, that worked exactly as I needed it to work.

Jeff Boyce said:
Your query employs (in technical jargon) an "equi-join", which only shows
rows when ALL joined recordsets have matching join-field values.

One way to approach your situation is to first create/use a query that lists
all possible ...?district orgs? ..., then join that recordset to each of
your "period" datasets with a directional join. First drag your ID from
your "all" list to your period list matching field, then right-click on the
join line and change the join properties to "all of one list and any that
match from the other". Repeat for each additional "period" dataset.

This will display every ... ?district org? ... and any matching value from
your "period" queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

karaeloko said:
Need help with this,

This is a query that I'm using to consolidate other queries that perform
the
same calculations, only for different date periods.

SELECT [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF
FROM ((((1stPeriod INNER JOIN 2ndPeriod ON [1stPeriod].FltNumOrgDst =
[2ndPeriod].FltNumOrgDst
GROUP BY [1stPeriod].FltNumOrgDst, [1stPeriod].ExpLF, [2ndPeriod].ExpLF;

The problem I have with it, is that if a FltNumOrgDst doesn't appear in
all
the other queries, it eliminates from the consolidating query. It doesn't
return blank or null values I think.

I need it to return every single value for FltNumOrgDst, even if it
doesn't
appear in a specific period.

thank you for your help
Click to expand...
Click to expand...
 
Back
Top