Query to Include Empty Records

[Guest]

I have two tables. The first is SalesData. The fields are Month, Year, Sales. The other Table is ExternalData. This table contains two fields called CreditAmount and one called CreditDate. Not every month has credits. I want to write a query that displays the following

Month Year Sales CreditAmount Credit%ofSale
January 2003 $200,000 $5,000 .025
February 2003 $250,000 $0 0
March 2003 $225,000 $7,500 .033
April 2003 $300,000 $0 0
Etc..

The problem I am having is that every month has sales. But not every month has credits
I want to still display the month but leave the credit field blank or with zeros. I do no
need to show the credit date field. Only the total for the month. The querie
I have written only display months with credits in them. How can I show all records wit
sales data and the credit data for corresponding months. Any help would be greatly apprectiated

-Tim

 

[Steve Schapel]

Tim,

In your query, you need a Left Join between the tables. In the query
design window, double-click on the join line and then select the
option that says something about "all records from SalesData and
matching records from Credit". Then, make a calculated field in the
query Nz([CreditAmount],0) to show zero for months where no credits.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thanks Steve! I had struggled all day with this. Works like a charm!

 

[Guest]

Steve, this works great but I have ran into another problem. I have two fields in my query that pull from different tables. One is Sales that pulls from the SalesData table the other is ApprCredit (approved credit) that pulls from the ExternalData table. The query fields look like this

Sales Credits: Sum((Nz([ApprCredit],0))) Percent: [Credits]/[Sales
SalesData
Group By Expression Group B

For some reason, (I am a beginner) when I run the query it prompts me for the value of Sales. If I just hit OK withou
entering any data it still works. If I enter any value for Sales it still works. In other words the query works fine regardless
So it's not actually using the value it's prompting for. How do I make it stop prompting for the Sales vaue. Any ideas

Thanks

Tim

 

[Steve Schapel]

Sorry, Tim, I've drawn a blank here... I can't think what might be
causing this behaviour. Might be an idea to repost the question as a
new thread, to increase the chances of someone else having a look at
it.

- Steve Schapel, Microsoft Access MVP