A simple database I would like to querie

[morlan]

Hello,

I was hoping some of you Access geniuses could help me with a simple task. Here is an example of my database:


Sales person - Location of sale - Day
==================================
Sue - Cork - Mon 01
Sue - Cork - Mon 01
Tom - Galway - Mon 01
Ben - Dublin - Mon 01
Sue - Cork - Tue 02
Sue - Cork - Tue 02
Ben - Dublin - Tue 02
Ben - Dublin - Tue 02
Ben - Dublin - Tue 02
Tom - Galway - Wed 03
Tom - Galway - Wed 03
Rory - Galway - Wed 03
Rory - Galway - Wed 03


Each row indicates a sale the employee has made on a given day.

I want to be able to go into MS Access and ask it "How many sales did Sue do on Monday the 1st?" and it would reply with "2"
or "How many sales were made in Galway on Wed the 3rd". the reply would be "4".

Is this easy to do? Can anyone point me in the right direction?

Thanks in advance

 

[Mile-O]

I'd suggest rather than store Mon-01 you store the actual date of the sale as what happens if, over the course of a year, three months start with a Monday and you want to know how many items were sold on Mon-01 then you would get the total for all of those months.

If you change to storing the actual date instead of those details then you'll find it easier to use the DCount domain aggregate function or the Count aggregate function.

You can look these both up in Access Help.

 

[morlan]

I've been looking in help the past half hours and I'm just lost. Can you give a quick example?

 

[morlan]

Could anybody show me a quick example??

 

[Rich]

Use a totals query

 

[morlan]

if someone could give me a simple step by step to start me off it would really help. Thanks

 

[Mile-O]

On the database window

Select Queries, hit "New"

Use the Simple Query Wizard

Follow it through, ensuring you select Summary somewhere within and the column you want to count on.

 

[morlan]

I can use:

=Count([SalesPerson])

and this will just count the amount of sales of all sale people. How can I get it to count how many sales of just Sue. Something todo with criteria?? Please help

 

[morlan]

OK I have figured out how to get it to count

=Sum([SalesExec]="Sue")


This will count how many sales sue did.

Can I make the forumla ask me which person I want to choose instead of having sue in the formula.?

 

[Mile-O]

Why don't you make a form instead whereby you can select the person from a combobox?