How to use colmn headings in paramter query

  • Thread starter Thread starter apex77
  • Start date Start date
A

apex77

Sorry if this has been asked before, but I am not able to locate it if it
has. But here is my delima:

I need to be able to select the salesperson sales based on the month (1-12)
from table 1. I* can create a parameter query, but am not sure hwo to query
onlya certain month.

table1
ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12
1 joe 12 0 0 2 12 0 1 1 5 7 8 10
2 steve 2 5 6 18 0 0 2 3 4 11 12 1

select * from table1 where salesperson =joe
and ....

that is where I get stuck. I need to be able to select a column heading
based on the month.
Thanks for any help.
 
The problem is that you are using Access as a spreadsheet. Having the months
in different columns is a sure sign of that and you'll have no end of
problems until you fix it.

You should have a Salesmen table that looks something like this:

ID salesperson .... and all the other stuff about that person such as
phone number.

ID salesperson
1 Joe
2 Steve

Then you should have a sales table looking something like

SalesID ID SalesMonth SalesAmount
1 1 1/1/2010 12
2 1 4/1/2010 2
3 2 1/1/2010 2
4 2 2/2/2010 5

Then you create a query that joins the two tables by the ID field. You can
just query on the SalesMonth as needed. The SalesMonth field should be a
date/time with a day (probably just the 1st) month and year. That way you can
use the same database for multiple years.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
Hi,

You can achieve what you're after using a query like this:
SELECT salesperson, [1] FROM table1 WHERE salesperson="joe";

Change [1] to the number of the month you want to query.

*But*, passing the month as a parameter to that query would be a non-trivial
exercise. Basically you'd need a separate query for each month.

The reason is, that that table has a bad case of the spreadsheets, and
really needs to be normalised. You can do this with a UNION query:

SELECT salesperson, 1 AS SalesMonth, [1] FROM table1
UNION SELECT salesperson, 2 AS SalesMonth, [2] AS Result FROM table1
UNION SELECT salesperson, 3 AS SalesMonth, [3] AS Result FROM table1
UNION SELECT salesperson, 4 AS SalesMonth, [4] AS Result FROM table1
 
Back
Top