Varying the field referenced in SQL

[Ifonly]

To save me describing the circumstances exactly I'll use a simple example that illustrates the problem.

Imagine a table called "rain" the columns are headed city, day1, day2......day365
In this table we record a yes or a no (-1 or 0) whether it rained in a particular city or town on that day of the year.

I want the user to be able to select the number from 1 to 365 and get a list of cities where it rained on that day.

A single SQL for day1 would look something like
SELECT Rain.city
FROM Rain
WHERE rain.day1=-1;

It is no biggy to write 365 queries like this using a mailmerge or similar program to change the last line to
WHERE rain.day2=-1;
WHERE rain.day3=-1;
etc
but I was wondering if there was some way to get the "day1" bit to come from a list. The user could then pick from that list something like day200 and the SQL would run using that field.

So far all I've come up with is either 365 queries or make a new table that is set up differently eg city, dayno, yes

 

[Ian Mac]

On 2003-02-17 14:37, Ifonly wrote:
To save me describing the circumstances exactly I'll use a simple example that illustrates the problem.

Imagine a table called "rain" the columns are headed city, day1, day2......day365
In this table we record a yes or a no (-1 or 0) whether it rained in a particular city or town on that day of the year.

I want the user to be able to select the number from 1 to 365 and get a list of cities where it rained on that day.

A single SQL for day1 would look something like
SELECT Rain.city
FROM Rain
WHERE rain.day1=-1;

It is no biggy to write 365 queries like this using a mailmerge or similar program to change the last line to
WHERE rain.day2=-1;
WHERE rain.day3=-1;
etc
but I was wondering if there was some way to get the "day1" bit to come from a list. The user could then pick from that list something like day200 and the SQL would run using that field.

So far all I've come up with is either 365 queries or make a new table that is set up differently eg city, dayno, yes

I think a further explaination is needed?!

365 columns (+1 for city) is a huge number (for Access anyway),
how many cities do you have?

It may be more practical have you 'days' as a column heading, then you can easily query the day where = 200

It sounds like you need a cross tab query?!

OR (baring in mind you haven't explained the full problem) as in your eg you're dealing with -1 or 0 if you just add entries to a table that are -1 say, you can then query just 2 columns.

Each entry would be:

City Day
E 1
J 10
P 9
H 4
Q 2
P 7
M 10
C 4
G 2
H 6
R 10
M 5
O 1
C 1
G 7
I 3
F 3
D 10
O 5

You then don't need to wonder if rained, but query for City or Day or Both.

I hope I've explained myself, it's late.
I think the way in which you order your data needs to be looked at.

Hope this helps, any questions, ask!






_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2003-02-18 18:54

 

[dk]

On 2003-02-17 14:37, Ifonly wrote:
To save me describing the circumstances exactly I'll use a simple example that illustrates the problem.

Imagine a table called "rain" the columns are headed city, day1, day2......day365
In this table we record a yes or a no (-1 or 0) whether it rained in a particular city or town on that day of the year.

I want the user to be able to select the number from 1 to 365 and get a list of cities where it rained on that day.

A single SQL for day1 would look something like
SELECT Rain.city
FROM Rain
WHERE rain.day1=-1;

It is no biggy to write 365 queries like this using a mailmerge or similar program to change the last line to
WHERE rain.day2=-1;
WHERE rain.day3=-1;
etc
but I was wondering if there was some way to get the "day1" bit to come from a list. The user could then pick from that list something like day200 and the SQL would run using that field.

So far all I've come up with is either 365 queries or make a new table that is set up differently eg city, dayno, yes

To add to what Ian said. You won't be able to use 365 fields because Access will only let you use 256 fields. As you alluded to, change your table design to have 3 fields (city, date, yes/no). As Ian pointed out, this will eliminate the problem you currently have. It will also mean that your table adheres to established db design standards/rules.

_________________
<font face="Arial Black" color="#0000FF">Hope this helps,
DK</font>
This message was edited by dk on 2003-02-18 19:04

 

[Ian Mac]

You won't be able to use 365 fields because Access will only let you use 256 fields

Actually (not tried to find to answer) I believe it's 450.
I remember a project I was given that needed to allocate 448 seats at our work +3 or 5 extra fields and couldn't be done.

To the author of the post:
My suggestion to them was that which I have given you.
BUT! my work insisted on 48 1/2hr fields, don't make their mistake, queries on that DB run for ~ 5mins to get the result required!!

Think of it this way, if I work 9-5 Monday to friday BUT I have a database with 1hourly slots 7days a week.
A query on that DB would have to look at 168 items to see if I was in, like your -1/0

BUT if I only have the hours I worked stored it has only 40 item to find. A big difference when you're looking at 356 days.

 

[dk]

We're both wrong, it's 255 fields

http://support.microsoft.com/default.aspx?scid=kb;en-us;302524