How to use colmn headings in paramter query

[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.

 

[Jerry Whittle]

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.

 

[Tokyo Alex]

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