Difficult Query

O

oldstonebuddha

I have been asked to pull some summary data out of a table, and due to the
structure, can't seem to get it. Any help would be appreciated.

The fields in the table look something like this:
TestID TestCode Area CFU OrgID1 OrgID2 OrgID3 OrgID4 OrgID5 OrgID6 OrgID7
OrgID8 OrgID9 OrgID10 Notes

Each record represents a microbiology monitoring test. The fields OrgID1,
OrgID2, OrgID3 etc. contain the name of the organism that was identified from
that test. The record has room for up to ten organisms.

Here's the problem: I need to summarize how many times a particular organism
has come up, but it may be in different ID fields.

For example, if I wanted the number of times ""Bacillus spp." has come up:
Record 1 - OrgID1 = "Bacillus spp."
Record 1 - OrgID2 = "CNS"

Record 2 - OrgID1 = "Staph A."
Record 2 - OrgID2 = "Bacillus spp."

Record 3 - OrgID1 = "Staph A."
Record 3 - OrgID2 = "CNS"
Record 3 - OrgID3 = "Bacillus spp."

The answer I'm looking for in this example is two. But I can't seem to
design a query that will look for a particular organism across different
fields. I tried a select query that used ="Bacillus spp." or Is Null as the
critera for the OrgIDFields. But if it doesn't find "Bacillus spp." in the
OrgID1 field, then it won't find it in the OrgID2 field.

Help! I've also tried pivot tables, with the same luck.

Thanks!
Scott D. Collins
 
G

Gina

This isn't the prettiest way, but it worked for me:

SELECT Count(Table1.ID) AS CountOfID
FROM Table1
WHERE (((Table1.f1)="x")) OR (((Table1.f2)="x")) OR (((Table1.f3)="x")) OR ((
(Table1.f4)="x")) OR (((Table1.f5)="x"));

The way to acheive this in the query designer is to put each criteria on a
seperate row. This gives you the "or" effect.

Gina
 
M

[MVP] S.Clark

The problem is that you have a spreadsheet, not a normalized table. Thus,
Access can't really help you. You can do a CountIF() in Excel easier than
you can do the count in Access.

If you were to normalize the data into a child table, then Access would be
the way to go.
 
L

Liz C

In the query grid under Criteria, put Like "baci" by the first OrgID, put the
same thing, only on the next line for the next OrgID, and so on. Be sure you
put the criteria on a seperate line for each OrgID and it will work.
 
C

Chris2

oldstonebuddha said:
I have been asked to pull some summary data out of a table, and due to the
structure, can't seem to get it. Any help would be appreciated.

The fields in the table look something like this:
TestID TestCode Area CFU OrgID1 OrgID2 OrgID3 OrgID4 OrgID5 OrgID6 OrgID7
OrgID8 OrgID9 OrgID10 Notes
Click to expand...

oldstonebuddha,

I see one general solution has been proposed earlier, but if you
have control or influence over this database, then the rest of my
post is in regards to that.

This table has repeating columns. This goes against the database
normalization rule called "1st Normal Form". (See the links I have
provided below for more infomration on database normalization.)

The table structure you have given is why you are experiencing
trouble retrieving the data you want.

Try:

Tables:

CREATE TABLE Oganisms
(OrganismID INTEGER
,CommonName TEXT(255)
,Description MEMO
,CONSTRAINT pk_MicroOrganisms
PRIMARY KEY (MicroOrganism)
)

CREATE TABLE Tests
(TestID INTEGER
,TestName TEXT(255)
,TestDescription MEMO
,CONSTRAINT pk_Test
PRIMARY KEY (TestID)
)

CREATE TABLE OrganismTests
(OrganismTestID INTEGER
,OrganismID INTEGER
,TestID INTEGER
,TestDate DATETIME
,CONSTRAINT pk_OrganismTests
PRIMARY KEY (OrganismTestID)
,CONSTRAINT fk_OrganismTests_Organisms_OrganismID
FOREIGN KEY (OrganismID)
REFERENCES Organisms (OrganismID)
,CONSTRAINT fk_OrganimsTests_Tests_TestID
FOREIGN KEY (TestID)
REFERENCES Tests (TestID)
)

Here's the problem: I need to summarize how many times a particular organism
has come up, but it may be in different ID fields.

For example, if I wanted the number of times ""Bacillus spp." has
Click to expand...
come up:

Untested (sorry, but any mistakes should only be typos, the idea is
the correct one).

SELECT COUNT(OT1.OrganismID) AS CountOfOrganism
FROM (OrganismTests AS OT1
INNER JOIN
Tests AS T1
ON OT1.TestID = T1.TestID)
INNER JOIN
Organisms AS O1
ON OT1.OrganismID = O1.OrganismID
WHERE O1.CommonName = "Bacillus sup."

Note: Technically, the JOIN to Tests isn't required here, but I
included it anyway based on the probability that information from
Tests might also be needed for similar queries (and so its inclusion
is just an overall part of the example).


The answer I'm looking for in this example is two. But I can't seem to
design a query that will look for a particular organism across different
fields. I tried a select query that used ="Bacillus spp." or Is Null as the
critera for the OrgIDFields. But if it doesn't find "Bacillus spp." in the
OrgID1 field, then it won't find it in the OrgID2 field.

Help! I've also tried pivot tables, with the same luck.

Thanks!
Scott D. Collins
Click to expand...


Database Normalization:

---------------------------

Googling on Database Normalization will provide many good articles.
Most of them are written for other database professionals (the
actual written rules of Database Normalization will throw you for a
loop if you don't know exactly what they are talking about).

Database Normalization is a process of decomposition, of breaking
down information until you get down the basic elements that describe
entities, and making sure that the information kept about an entity
(i.e. "table") *really* belongs to it, and not to something else (or
somewhere else).

---------------------------

Database Normalization:

Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
(I quite like this whole site, since it has a handy menu on the
right
describing many important aspects of database normalization and
modeling.)
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.
 
T

Tom Ellison

Dear Stone:

As others have said, your structure is the problem. Short of restructuring
the data, or as a step toward doing that, you can create a norlamized
appearance to this data with a "normalizing union query" and then you can
query the results of this normalizing query as though the data were
normalized in the first place. In addition, you can use a form of this
normalizing query to move the data into a properly constructed table
permanently.

For the purposes you show, you would need this:

SELECT TestID, TestCode, Area, CFU, 1 as Source, OrgID1
FROM YourTable
WHERE OrgID1 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 2 as Source, OrgID2
FROM YourTable
WHERE OrgID2 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 3 as Source, OrgID3
FROM YourTable
WHERE OrgID3 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 4 as Source, OrgID4
FROM YourTable
WHERE OrgID4 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 5 as Source, OrgID5
FROM YourTable
WHERE OrgID5 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 6 as Source, OrgID6
FROM YourTable
WHERE OrgID6 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 7 as Source, OrgID7
FROM YourTable
WHERE OrgID7 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 8 as Source, OrgID8
FROM YourTable
WHERE OrgID8 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 9 as Source, OrgID9
FROM YourTable
WHERE OrgID9 IS NOT NULL
UNION ALL
SELECT TestID, TestCode, Area, CFU, 10 as Source, OrgID10
FROM YourTable
WHERE OrgID10 IS NOT NULL

Here, I have added a column "Source" which keeps the original order to the
OrgIDs. This can be useful. It also makes the rows unique. If someone has
entered the same OrgID in OrgID4 and OrgID9, they will be unique. While
this may be an error, it is not necessarily the case you want this to cause
problems at this time.

From the above query, a Count() of each bacillus is simple:

SELECT OrgID, COUNT(*)
FROM TheQuery
GROUP BY OrgID

Note that, if an OrgID occurs twice in the same original row, it will be
counted twice.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
 
T

Tom Ellison

I would have preferred I had spelled "norlamized" as "normalized" OK?

Tom Ellison
 
T

Tom Ellison

As in, "for your infomration, the table is not norlamized." LOL.

Not that's furmy. (sight gag)

Tom Ellison
 
C

Chris2

Tom Ellison said:
I would have preferred I had spelled "norlamized" as "normalized" OK?

Tom Ellison
Click to expand...

Tom Ellison,

That's alright, I spelled "information" as "infomration" in this
same thread. ;)


Sincerely,

Chris O.
 
Top