Including all records

  • Thread starter Thread starter Erin
  • Start date Start date
E

Erin

Hi All,

I have a table with customer information and a table with
transactions. I have a query which brings these two
together and a report which uses this query to list the
customers and the related transactions for each. Of
course if the customer has no transactions the customer
does not show up on the query and, thus, not on the
report. Can someone point me in the right direction to
include all customers on the report? Any help would be
appreciated. Thanks.

Erin
 
Open your query in design view.
Double-click the line joining the two tables.
Choose the option:
All records from Customer, and any matches from Transactions.
 
Thanks Allen, but when I went to try it I found that
button was already selected, and the SQL does indeed show
a left join. Any other ideas why the two customers who
don't have transactions would be excluded? Thanks.

Erin
 
Allen,

For simplicity I said I was using two tables with
customers and transactions, but actually it is a query
named BUDGET REPORT (PARTNERS) and another named BUDGET
REPORT (CONTRIBUTIONS). When I run the BUDGET REPORT
(PARTNERS) query with the criteria YEAR = 2003 I get 29
partners, but when I run the query shown below with the
criteria YEAR = 2003 the two partners without
contributions are missing. I'm editing an old existing
database so it's a bit messy. I could easily be missing
something obvious. Thanks for taking a look.

Erin

SELECT DISTINCTROW [BUDGET REPORT (PARTNERS)].NUMBER,
[BUDGET REPORT (PARTNERS)].PARTNAME, [BUDGET REPORT
(PARTNERS)].ALPHA, [BUDGET REPORT (PARTNERS)].[UNITS]*2 AS
BUDGET, [BUDGET REPORT (PARTNERS)].YEARNUM, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY, [BUDGET REPORT
(CONTRIBUTIONS)].DATE, [BUDGET REPORT
(CONTRIBUTIONS)].CHECKNUM, [BUDGET REPORT
(CONTRIBUTIONS)].AMOUNT, [BUDGET REPORT
(CONTRIBUTIONS)].CAPAMT, Format([DATE],"yyyy") AS YEARDATE
FROM [BUDGET REPORT (PARTNERS)] LEFT JOIN [BUDGET REPORT
(CONTRIBUTIONS)] ON [BUDGET REPORT (PARTNERS)].NUMBER =
[BUDGET REPORT (CONTRIBUTIONS)].NUMBER
WHERE ((([BUDGET REPORT (PARTNERS)].YEARNUM)=[FORMS]!
[REPORTS]![YEAR]) AND ((Format([DATE],"yyyy"))=[FORMS]!
[REPORTS]![YEAR]))
ORDER BY [BUDGET REPORT (PARTNERS)].ALPHA, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY;
 
Your WHERE clause includes criteria on both tables.
You need to specify that you want to include the Null fields from the
related table.

Change the WHERE clause to:

WHERE (([BUDGET REPORT (PARTNERS)].YEARNUM =
[FORMS]![REPORTS]![YEAR])
AND
((Year([BUDGET REPORT (CONTRIBUTIONS)].[DATE]) =
[FORMS]![REPORTS]![YEAR])
OR [BUDGET REPORT (CONTRIBUTIONS)].[DATE] Is Null)))

More information about nulls in foreign keys in article:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

There is also an issue of data types. Format() returns a Variant of subtype
String. If the text box is unbound, its data type is undefined. It would be
better to use the Year() function rather than the Format() function to get a
Number. Then set the Format property of the text box on your form to:
General Number
so Access knows it's a number also.

The field named "Date" will probably work in this context, but Date is a
reserved word in VBA - used for the system date. Consider changing the name:
sooner or later it will bite you. Year is also a function name, so you might
want to rename the text box to something like txtYear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Erin said:
Allen,

For simplicity I said I was using two tables with
customers and transactions, but actually it is a query
named BUDGET REPORT (PARTNERS) and another named BUDGET
REPORT (CONTRIBUTIONS). When I run the BUDGET REPORT
(PARTNERS) query with the criteria YEAR = 2003 I get 29
partners, but when I run the query shown below with the
criteria YEAR = 2003 the two partners without
contributions are missing. I'm editing an old existing
database so it's a bit messy. I could easily be missing
something obvious. Thanks for taking a look.

Erin

SELECT DISTINCTROW [BUDGET REPORT (PARTNERS)].NUMBER,
[BUDGET REPORT (PARTNERS)].PARTNAME, [BUDGET REPORT
(PARTNERS)].ALPHA, [BUDGET REPORT (PARTNERS)].[UNITS]*2 AS
BUDGET, [BUDGET REPORT (PARTNERS)].YEARNUM, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY, [BUDGET REPORT
(CONTRIBUTIONS)].DATE, [BUDGET REPORT
(CONTRIBUTIONS)].CHECKNUM, [BUDGET REPORT
(CONTRIBUTIONS)].AMOUNT, [BUDGET REPORT
(CONTRIBUTIONS)].CAPAMT, Format([DATE],"yyyy") AS YEARDATE
FROM [BUDGET REPORT (PARTNERS)] LEFT JOIN [BUDGET REPORT
(CONTRIBUTIONS)] ON [BUDGET REPORT (PARTNERS)].NUMBER =
[BUDGET REPORT (CONTRIBUTIONS)].NUMBER
WHERE ((([BUDGET REPORT (PARTNERS)].YEARNUM)=[FORMS]!
[REPORTS]![YEAR]) AND ((Format([DATE],"yyyy"))=[FORMS]!
[REPORTS]![YEAR]))
ORDER BY [BUDGET REPORT (PARTNERS)].ALPHA, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY;

-----Original Message-----
Erin, could you post the SQL statement?
(From SQL View in query design)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
Click to expand...
 
Allen,

Now I'm kicking myself. Worked like a charm. Thanks so
much for sticking with me, and thanks for the tips. As I
said this is an old database from before my time. Once I
get it running again I'll take a look at your suggestions.

Erin
-----Original Message-----
Your WHERE clause includes criteria on both tables.
You need to specify that you want to include the Null fields from the
related table.

Change the WHERE clause to:

WHERE (([BUDGET REPORT (PARTNERS)].YEARNUM =
[FORMS]![REPORTS]![YEAR])
AND
((Year([BUDGET REPORT (CONTRIBUTIONS)].[DATE]) =
[FORMS]![REPORTS]![YEAR])
OR [BUDGET REPORT (CONTRIBUTIONS)].[DATE] Is Null)))

More information about nulls in foreign keys in article:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

There is also an issue of data types. Format() returns a Variant of subtype
String. If the text box is unbound, its data type is undefined. It would be
better to use the Year() function rather than the Format () function to get a
Number. Then set the Format property of the text box on your form to:
General Number
so Access knows it's a number also.

The field named "Date" will probably work in this context, but Date is a
reserved word in VBA - used for the system date. Consider changing the name:
sooner or later it will bite you. Year is also a function name, so you might
want to rename the text box to something like txtYear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen,

For simplicity I said I was using two tables with
customers and transactions, but actually it is a query
named BUDGET REPORT (PARTNERS) and another named BUDGET
REPORT (CONTRIBUTIONS). When I run the BUDGET REPORT
(PARTNERS) query with the criteria YEAR = 2003 I get 29
partners, but when I run the query shown below with the
criteria YEAR = 2003 the two partners without
contributions are missing. I'm editing an old existing
database so it's a bit messy. I could easily be missing
something obvious. Thanks for taking a look.

Erin

SELECT DISTINCTROW [BUDGET REPORT (PARTNERS)].NUMBER,
[BUDGET REPORT (PARTNERS)].PARTNAME, [BUDGET REPORT
(PARTNERS)].ALPHA, [BUDGET REPORT (PARTNERS)].[UNITS]*2 AS
BUDGET, [BUDGET REPORT (PARTNERS)].YEARNUM, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY, [BUDGET REPORT
(CONTRIBUTIONS)].DATE, [BUDGET REPORT
(CONTRIBUTIONS)].CHECKNUM, [BUDGET REPORT
(CONTRIBUTIONS)].AMOUNT, [BUDGET REPORT
(CONTRIBUTIONS)].CAPAMT, Format([DATE],"yyyy") AS YEARDATE
FROM [BUDGET REPORT (PARTNERS)] LEFT JOIN [BUDGET REPORT
(CONTRIBUTIONS)] ON [BUDGET REPORT (PARTNERS)].NUMBER =
[BUDGET REPORT (CONTRIBUTIONS)].NUMBER
WHERE ((([BUDGET REPORT (PARTNERS)].YEARNUM)=[FORMS]!
[REPORTS]![YEAR]) AND ((Format([DATE],"yyyy"))=[FORMS]!
[REPORTS]![YEAR]))
ORDER BY [BUDGET REPORT (PARTNERS)].ALPHA, [BUDGET REPORT
(CONTRIBUTIONS)].CHARNAME, [BUDGET REPORT
(CONTRIBUTIONS)].CATEGORY;

-----Original Message-----
Erin, could you post the SQL statement?
(From SQL View in query design)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but when I went to try it I found that
button was already selected, and the SQL does indeed show
a left join. Any other ideas why the two customers who
don't have transactions would be excluded? Thanks.

Erin
-----Original Message-----
Open your query in design view.
Double-click the line joining the two tables.
Choose the option:
All records from Customer, and any matches from
Transactions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Hi All,

I have a table with customer information and a table
with
transactions. I have a query which brings these two
together and a report which uses this query to
Click to expand...
list
the
customers and the related transactions for each. Of
course if the customer has no transactions the customer
does not show up on the query and, thus, not on the
report. Can someone point me in the right
Click to expand...
direction
to
include all customers on the report? Any help
Click to expand...
would
be
appreciated. Thanks.

Erin


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top