Empty fields in query

[Cass]

I will try to give as much detail as possible. I have two queries.
These queries are pulling data from a table which is exported from
another program daily and therefore columns cannot be modified. The
name of that table is "Shipments 2006".

Here is the first query:
SELECT [SHIPMENTS 2006].GROUP, Count(*) AS [TOTAL SHIPMENTS],
[SHIPMENTS 2006].WEEK
FROM [SHIPMENTS 2006]
GROUP BY [SHIPMENTS 2006].GROUP, [SHIPMENTS 2006].WEEK;

Here is the second query:
SELECT [SHIPMENTS 2006].GROUP, Count(*) AS [EARLY ON TIME SHIPMENTS],
[SHIPMENTS 2006].WEEK
FROM [SHIPMENTS 2006]
WHERE ((([SHIPMENTS 2006].DIFF)=0 Or ([SHIPMENTS 2006].DIFF)>0))
GROUP BY [SHIPMENTS 2006].GROUP, [SHIPMENTS 2006].WEEK;

These queries are being joined in a MAKE-TABLE query that we use to
make weekly reports:
SELECT [Work Center Capacity].[View Order], [ALL SHIPMENTS 2006 Query
by WEEK (WC)].GROUP, [Work Center Capacity].[Work Center], [Class Group
Codes].[Work Center Description], [ALL SHIPMENTS 2006 Query by WEEK
(WC)].[TOTAL SHIPMENTS], [EARLY ON TIME 2006 Query by WEEK (WC)].[EARLY
ON TIME SHIPMENTS], [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK INTO
[ON TIME (WC) SHIPMENTS SUMMARY by WEEK 06]
FROM (([Class Group Codes] INNER JOIN [ALL SHIPMENTS 2006 Query by WEEK
(WC)] ON [Class Group Codes].[JDE Product Group] = [ALL SHIPMENTS 2006
Query by WEEK (WC)].GROUP) INNER JOIN [EARLY ON TIME 2006 Query by WEEK
(WC)] ON ([Class Group Codes].[JDE Product Group] = [EARLY ON TIME 2006
Query by WEEK (WC)].GROUP) AND ([ALL SHIPMENTS 2006 Query by WEEK
(WC)].WEEK = [EARLY ON TIME 2006 Query by WEEK (WC)].WEEK)) INNER JOIN
[Work Center Capacity] ON [Class Group Codes].[Work Center] = [Work
Center Capacity].[Work Center]
GROUP BY [Work Center Capacity].[View Order], [ALL SHIPMENTS 2006 Query
by WEEK (WC)].GROUP, [Work Center Capacity].[Work Center], [Class Group
Codes].[Work Center Description], [ALL SHIPMENTS 2006 Query by WEEK
(WC)].[TOTAL SHIPMENTS], [EARLY ON TIME 2006 Query by WEEK (WC)].[EARLY
ON TIME SHIPMENTS], [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK
HAVING ((([ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK)=[Enter Week:
Week #]))
ORDER BY [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK;


In reality, I was told the report had to be grouped by WORK CENTER. But
because work center is not in the "shipments 2006" table I could not
find a way to display all the data and do this. When I added the table
with work center in it and joined it to the shipments table by GROUP
because that was the only relationship that they had, the null fields
did not appear in the query. However when I removed that extra table
and sorted by group, the null fields appeared.

This is my problem. My totals are not coming up clear on this table
because the records with empty values are not appearing in my
make-table query but they are showing up in the two select queries.
Anyone know how I could make this work? I hope you can understand what
I am trying to do, if not let me know.

 

[Jerry Whittle]

You have an INNER JOIN between the two record sets. If one side does not have
a matching record with the other, data from neither side will show.

You need to do a Left or Right Join. In the Query Design View double click
on the black line(s) between the tables/queries and select one of the other
options in the dialog box.

You may run into a couple of problems.

(1) You have multiple joins betweent the two record sets and might have to
play with the combinations of joins to get the desired results.

(2) It's possible that you might need both Right and Left joins to get
records from both sides in one query. Access does not support this type of
Outer join. If you run into this problem, it's most likely that your database
is not properly normalized to support your needs.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I will try to give as much detail as possible. I have two queries.
These queries are pulling data from a table which is exported from
another program daily and therefore columns cannot be modified. The
name of that table is "Shipments 2006".

Here is the first query:
SELECT [SHIPMENTS 2006].GROUP, Count(*) AS [TOTAL SHIPMENTS],
[SHIPMENTS 2006].WEEK
FROM [SHIPMENTS 2006]
GROUP BY [SHIPMENTS 2006].GROUP, [SHIPMENTS 2006].WEEK;

Here is the second query:
SELECT [SHIPMENTS 2006].GROUP, Count(*) AS [EARLY ON TIME SHIPMENTS],
[SHIPMENTS 2006].WEEK
FROM [SHIPMENTS 2006]
WHERE ((([SHIPMENTS 2006].DIFF)=0 Or ([SHIPMENTS 2006].DIFF)>0))
GROUP BY [SHIPMENTS 2006].GROUP, [SHIPMENTS 2006].WEEK;

These queries are being joined in a MAKE-TABLE query that we use to
make weekly reports:
SELECT [Work Center Capacity].[View Order], [ALL SHIPMENTS 2006 Query
by WEEK (WC)].GROUP, [Work Center Capacity].[Work Center], [Class Group
Codes].[Work Center Description], [ALL SHIPMENTS 2006 Query by WEEK
(WC)].[TOTAL SHIPMENTS], [EARLY ON TIME 2006 Query by WEEK (WC)].[EARLY
ON TIME SHIPMENTS], [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK INTO
[ON TIME (WC) SHIPMENTS SUMMARY by WEEK 06]
FROM (([Class Group Codes] INNER JOIN [ALL SHIPMENTS 2006 Query by WEEK
(WC)] ON [Class Group Codes].[JDE Product Group] = [ALL SHIPMENTS 2006
Query by WEEK (WC)].GROUP) INNER JOIN [EARLY ON TIME 2006 Query by WEEK
(WC)] ON ([Class Group Codes].[JDE Product Group] = [EARLY ON TIME 2006
Query by WEEK (WC)].GROUP) AND ([ALL SHIPMENTS 2006 Query by WEEK
(WC)].WEEK = [EARLY ON TIME 2006 Query by WEEK (WC)].WEEK)) INNER JOIN
[Work Center Capacity] ON [Class Group Codes].[Work Center] = [Work
Center Capacity].[Work Center]
GROUP BY [Work Center Capacity].[View Order], [ALL SHIPMENTS 2006 Query
by WEEK (WC)].GROUP, [Work Center Capacity].[Work Center], [Class Group
Codes].[Work Center Description], [ALL SHIPMENTS 2006 Query by WEEK
(WC)].[TOTAL SHIPMENTS], [EARLY ON TIME 2006 Query by WEEK (WC)].[EARLY
ON TIME SHIPMENTS], [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK
HAVING ((([ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK)=[Enter Week:
Week #]))
ORDER BY [ALL SHIPMENTS 2006 Query by WEEK (WC)].WEEK;


In reality, I was told the report had to be grouped by WORK CENTER. But
because work center is not in the "shipments 2006" table I could not
find a way to display all the data and do this. When I added the table
with work center in it and joined it to the shipments table by GROUP
because that was the only relationship that they had, the null fields
did not appear in the query. However when I removed that extra table
and sorted by group, the null fields appeared.

This is my problem. My totals are not coming up clear on this table
because the records with empty values are not appearing in my
make-table query but they are showing up in the two select queries.
Anyone know how I could make this work? I hope you can understand what
I am trying to do, if not let me know.