Missing entries

[LEE hepworth]

Hi All
Wonder if you can help
What I have is Sales Database run in Access 97.
I search on Sales in the Access Query
But say we have 10 Sales reps and only 7 have sales the query will only show those seven.
Is there anyway that the query can show the three that did not have any sales with the ones that did.
Many thanks

 

[dcx693]

I search on Sales in the Access Query

How exactly are you doing the search?

 

[Pat Hartman]

If you want a list of salespeople, you should get it from the salesperson table rather than the sales table. If you want a list of all salespeople and a summary of any sales they made for a given period, use a query that joins the salesperson table to the sales table with a LEFT join.

 

[LEE hepworth]

Displaying records

Hi All
What I have are two tables, One called Employees and the other called sales with a one to many ie one employee can have many sales

The problem is say one of the employees has no Sales they are not displayed in the query. What I want is all the employees displayed. Those with Sales and those with no Sales.
I search on a field called sales which produces -1 if its a sale and 0 if its not a sale and also on date(Parameter Query).
Using access 97

Also sometimes in other queries in which I have a parmeter query on date it sometimes askes me for the date twice. WHY when it should only be once?

 

[Pat Hartman]

The problem is say one of the employees has no Sales they are not displayed in the query

- the left join to the employees table will solve this problem.

Also sometimes in other queries in which I have a parmeter query on date it sometimes askes me for the date twice. WHY when it should only be once?

- this can happen when you have reports based on parameter queries. Rather than prompt for the parameters, use a form that has textboxes where the parameters are entered and have the queries reference those textboxes.

Where YourField = Forms!YourForm!YourTextBox;