need help with SQL and counting values across many columns

[DougP]

I have table with 20 columns Named 1 to 20
next I have 5 rows for each area so there could be a total of 100 samples, 5 by 20. There is a subform which shows the 100 boxes. When the user takes samples, they may only use some of the 100 or 15 next time they may use 22 of the 100 and so on.
I need to count across all 20 columns and 5 rows to get a total if the box has something in it. So I want to end up knowing they took 22 samples or 15 samples.
Can this be done with one SQL statement?

TIA


DougP

 

[lespaul]

The real problem is your data isn't normalized, you shouldn't have 20 fields with the same name and a different number.

Is there a way to identify the 5 "rows" (records?) that belong together? What you are going to eventually need to do is create a query that normalizes your data:

SELECT AllTheOtherFieldsYouNeed, Name1, "1" WHERE Name1 <> ''
UNION
SELECT AllTheOtherFieldsYouNeed, Name2, "2" WHERE Name2 <> ''
etc.
UNION
SELECT AllTheOTherFieldsYouNeed, Name20, "20" WHERE Name20 <> ''

now you can do a simple count query grouping by the other needed fields.

HTH


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[DougP]

you wrote:
Is there a way to identify the 5 "rows".
Yes there is a field named "Series" with the nubmers 1 to 5 for each of the five rows. I agree this is not the best way.
So how do I make a table which is then a subform to show 100 empty boxes that they can fill in any random numbers they need to. Currently they are doing this in Excel and I have the task of making it in Access. Its laid out in a 5x20 grid to make it easier for them to put the sample data into the correct box.

DougP

 

[lespaul]

What is it you are taking samples of? It really all depends on what kind of data you are collecting and how you want to report it. Let's say you are taking samples of water and each person has a specific section of river to sample and you need to track what person took the sample (that way you can determine which section of river it came from since there is a ONE to ONE relationship between the people taking the sample and the location of the sample). Now each person can take many samples on the same day, so now we have a one to many relationship between the date of the sample and the sample itself (since there will be MANY samples on ONE day). So assuming this is our scenario, I would have at the least, the following tables:
[tt]
RiverSection
SectionID
StartingMileMarker
EndingMileMarker
{anything else about the section of river}

Participants
ParticipantID
Name
Address
{anything else about the participant}

ParticipantSection
PSID
ParticipantID
SectionID
Startdate
EndDate
{I included a date range in case participants drop out and now some other participant is tracking that riversection}

SampleHeader
SHeaderID
PSID (identifies the person that did the sample and section that was sampled)
SampleDate

Samples
SampleID
SHeaderID
Measurement
[/tt]

Now, I just kinda threw together this example and I'm sure there are all kinds of things that I missed, but that's basically the process for developing the table structure. Once you get the tables set up, then it's time to go to user interface.

Have you read Fundamentals of Relational Database Design? It is good at helping you determine what your data is and how to set up the tables correctly.

Leslie