Reports using 2 different queries

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

Guest

I am trying to create a report from 2 queries. One query has 1000 records
the other has 500 hundred records. I want the report to have the 1000
records. The query with the 500 hundred records only has the contact name and
region but not for all the 1000 records. When I link them by Institution ID
I only get of course 500 hundred records. How can I get a report that has
the 1000 records with the 500 contact names and the other 500 records to
staty blank?
 
Change the join (the link) in the query. Double click on the line and
select the option that gives you ALL the records in the 1000 record query
and only matching in the 500 record query.
 
Thanks for replying but I already tried that and it does not work. I still
just get 500 records instead of 1000.
 
Then something else seems to be going on. Do you have a WHERE clause
(criteria) in the query? That could also cause the query to return less
records. For instance, if you put criteria against one of the fields in the
500 query, then that would eliminate all the rows where that field was null
and any records that did not meet the criteria.

Without more information on your query, it will be difficult to solve your
problem.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
No I don't have a Where Clause in a query. What I can tell you is that one
query is pulling from seven tables and one query. None of the queries have a
where caluse. The other query pulls from 4 tables and this one has the 500
records. I noticed that if I delete one particular table from the query that
has the 500 records, then I will get 1000 records in the report. But I need
the information from that table to show on the report, I only need one field
from that "particular table".
 
Here is the SQL
SELECT [Inst Base Data 1-19-06B2].IPEDS_CD, [US New Selectivity
2005].[Selectivity Ranking], [US New Selectivity 2005].[Inst Type], [US New
Selectivity 2005].Tier, [zHealth Advisor].[Health Advisor], [zMCAT
Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor] INNER JOIN [US New
Selectivity 2005] ON [zHealth Advisor].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) ON [zMCAT Contact].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) INNER JOIN [Inst Base Data 1-19-06B2] ON [zHealth
Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;
 
All your queries are INNER JOINS which means that there MUST be a match in
each table to return a row.

Since you didn't say WHICH table has the 500 records I can only suggest you
try changing INNER JOIN to LEFT JOIN on the appropriate table link. See if
that helps. This is what I suggested in the first response to you on 3-31
Guessing that the 500 record table is Inst Base Data 1-19-06B2 would change
the query to


SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
, [US New Selectivity 2005].[Selectivity Ranking]
, [US New Selectivity 2005].[Inst Type]
, [US New Selectivity 2005].Tier
, [zHealth Advisor].[Health Advisor]
, [zMCAT Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor]
INNER JOIN [US New Selectivity 2005]
ON [zHealth Advisor].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
ON [zMCAT Contact].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
LEFT JOIN [Inst Base Data 1-19-06B2]
ON [zHealth Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;



brown_eyes said:
Here is the SQL
SELECT [Inst Base Data 1-19-06B2].IPEDS_CD, [US New Selectivity
2005].[Selectivity Ranking], [US New Selectivity 2005].[Inst Type], [US
New
Selectivity 2005].Tier, [zHealth Advisor].[Health Advisor], [zMCAT
Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor] INNER JOIN [US New
Selectivity 2005] ON [zHealth Advisor].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) ON [zMCAT Contact].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) INNER JOIN [Inst Base Data 1-19-06B2] ON [zHealth
Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;


John Spencer said:
Then something else seems to be going on. Do you have a WHERE clause
(criteria) in the query? That could also cause the query to return less
records. For instance, if you put criteria against one of the fields in
the
500 query, then that would eliminate all the rows where that field was
null
and any records that did not meet the criteria.

Without more information on your query, it will be difficult to solve
your
problem.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
Click to expand...
Click to expand...
 
Oh I'm sorry the table that has the 500 records is the (US New Selectivity
2005)

John Spencer said:
All your queries are INNER JOINS which means that there MUST be a match in
each table to return a row.

Since you didn't say WHICH table has the 500 records I can only suggest you
try changing INNER JOIN to LEFT JOIN on the appropriate table link. See if
that helps. This is what I suggested in the first response to you on 3-31
Guessing that the 500 record table is Inst Base Data 1-19-06B2 would change
the query to


SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
, [US New Selectivity 2005].[Selectivity Ranking]
, [US New Selectivity 2005].[Inst Type]
, [US New Selectivity 2005].Tier
, [zHealth Advisor].[Health Advisor]
, [zMCAT Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor]
INNER JOIN [US New Selectivity 2005]
ON [zHealth Advisor].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
ON [zMCAT Contact].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
LEFT JOIN [Inst Base Data 1-19-06B2]
ON [zHealth Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;



brown_eyes said:
Here is the SQL
SELECT [Inst Base Data 1-19-06B2].IPEDS_CD, [US New Selectivity
2005].[Selectivity Ranking], [US New Selectivity 2005].[Inst Type], [US
New
Selectivity 2005].Tier, [zHealth Advisor].[Health Advisor], [zMCAT
Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor] INNER JOIN [US New
Selectivity 2005] ON [zHealth Advisor].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) ON [zMCAT Contact].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) INNER JOIN [Inst Base Data 1-19-06B2] ON [zHealth
Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;


John Spencer said:
Then something else seems to be going on. Do you have a WHERE clause
(criteria) in the query? That could also cause the query to return less
records. For instance, if you put criteria against one of the fields in
the
500 query, then that would eliminate all the rows where that field was
null
and any records that did not meet the criteria.

Without more information on your query, it will be difficult to solve
your
problem.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


Thanks for replying but I already tried that and it does not work. I
still
just get 500 records instead of 1000.

:

I am trying to create a report from 2 queries. One query has 1000
records
the other has 500 hundred records. I want the report to have the 1000
records. The query with the 500 hundred records only has the contact
name
and
region but not for all the 1000 records. When I link them by
Institution
ID
I only get of course 500 hundred records. How can I get a report that
has
the 1000 records with the 500 contact names and the other 500 records
to
staty blank?
Click to expand...
Click to expand...
Click to expand...
 
You can try the following. If it doesn't work, then try changing the JOINS
between the tables in the query grid view and see what results you get. I
won't be available anymore this afternoon.

SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
, [US New Selectivity 2005].[Selectivity Ranking]
, [US New Selectivity 2005].[Inst Type]
, [US New Selectivity 2005].Tier
, [zHealth Advisor].[Health Advisor]
, [zMCAT Contact].[MCAT Contact]
FROM ([zMCAT Contact] LEFT JOIN ([zHealth Advisor]
LEFT JOIN [US New Selectivity 2005]
ON [zHealth Advisor].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
ON [zMCAT Contact].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
INNER JOIN [Inst Base Data 1-19-06B2]
ON [zHealth Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;


brown_eyes said:
Oh I'm sorry the table that has the 500 records is the (US New Selectivity
2005)

John Spencer said:
All your queries are INNER JOINS which means that there MUST be a match
in
each table to return a row.

Since you didn't say WHICH table has the 500 records I can only suggest
you
try changing INNER JOIN to LEFT JOIN on the appropriate table link. See
if
that helps. This is what I suggested in the first response to you on
3-31
Guessing that the 500 record table is Inst Base Data 1-19-06B2 would
change
the query to


SELECT [Inst Base Data 1-19-06B2].IPEDS_CD
, [US New Selectivity 2005].[Selectivity Ranking]
, [US New Selectivity 2005].[Inst Type]
, [US New Selectivity 2005].Tier
, [zHealth Advisor].[Health Advisor]
, [zMCAT Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor]
INNER JOIN [US New Selectivity 2005]
ON [zHealth Advisor].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
ON [zMCAT Contact].IPEDS_CD=[US New Selectivity 2005].[IPEDS Code])
LEFT JOIN [Inst Base Data 1-19-06B2]
ON [zHealth Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;



brown_eyes said:
Here is the SQL
SELECT [Inst Base Data 1-19-06B2].IPEDS_CD, [US New Selectivity
2005].[Selectivity Ranking], [US New Selectivity 2005].[Inst Type], [US
New
Selectivity 2005].Tier, [zHealth Advisor].[Health Advisor], [zMCAT
Contact].[MCAT Contact]
FROM ([zMCAT Contact] INNER JOIN ([zHealth Advisor] INNER JOIN [US New
Selectivity 2005] ON [zHealth Advisor].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) ON [zMCAT Contact].IPEDS_CD=[US New Selectivity
2005].[IPEDS Code]) INNER JOIN [Inst Base Data 1-19-06B2] ON [zHealth
Advisor].IPEDS_CD=[Inst Base Data 1-19-06B2].IPEDS_CD;


:

Then something else seems to be going on. Do you have a WHERE clause
(criteria) in the query? That could also cause the query to return
less
records. For instance, if you put criteria against one of the fields
in
the
500 query, then that would eliminate all the rows where that field
was
null
and any records that did not meet the criteria.

Without more information on your query, it will be difficult to solve
your
problem.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


Thanks for replying but I already tried that and it does not work.
I
still
just get 500 records instead of 1000.

:

I am trying to create a report from 2 queries. One query has 1000
records
the other has 500 hundred records. I want the report to have the
1000
records. The query with the 500 hundred records only has the
contact
name
and
region but not for all the 1000 records. When I link them by
Institution
ID
I only get of course 500 hundred records. How can I get a report
that
has
the 1000 records with the 500 contact names and the other 500
records
to
staty blank?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top