writing a query to retrieve certain data from tables

[Guest]

I have 7 tables. When I want to use query wizard, it doesn’t let me to
select more than 5 tables. Why?
I want to write a query to check all tests that were in July (test date is a
field in all the tables). Or check all the ‘Pass’es tests in the past 3
months. Is there anyway (like writing SQL query) to check all tables for me
in this case?
I want to do this but I don’t know how
Select <all tables>
From <test database>
Where test result = pass
Or :
Where tests where between May to July

I appreciate your help.

 

[Guest]

Shery:

You have the basic idea of the query:

Select <all tables>
From <test database>
Where test result = pass

However, the structure is more detailed than this. If things were only as
simple as what you describe. =)

Could you please describe your tables, and what columns you are looking to
select, from each?

My first thought is that you probably don't need all 7 tables. 2 or 3 might
be enough to do the same thing, and would also make your querying easier.

Regardless, if you can post the table structures, someone (myself included)
should be able to provide some guidance in how to proceed.

Sharkbyte

 

[Rick B]

I guess my question would be why you have 7 tables? It sounds like your
data is pretty much the same in each table? If so, then your database
structure is flawed.

 

[Guest]

The problem is that these 7 tables are not related to each other. They have
their own specific lot#. Each table has 6 or 7 fields. I am working in a
biology lab and these 7 tables are 7 different samples that we test each day.
Something that these tables might have in common are: test date, test
result, or pH meter that these tests are using.
There test tables are;
1. control 1
2. control 2
3. Sterile water
4. Tab water
5. Reference1
6. Reference2
7. Reference3
Now, since in one day we might have 2 tests, we want to search the “passâ€es
tests that is done for example between june to july.
Each of these tables has it is own test result field.
Or all the tests that is done in July, 05 – we have a field that shows test
date.

And the worst query is that we have to search all the tables and look for
certain # that can be anywhere. This # is 2005055505.

For all of these, something that we should retrieve and is unique for each
table is ID# of the test.

 

[Guest]

Shery:

Can you provide the individual table structures? And, are you simply
looking for dates of "passed" tests? Where are the Pass/Fail data kept?

I still have to agree w/ Rick, at this point. Given what you have said, you
could easily shrink this db into about 3 tables, but I would like to see the
individual structures before I can be precise. And, if not, I would still
need them to provide you the SQL for the query you asked about.

Sharkbyte

 

[Guest]

Hi,
I wanted to send you the tables as attachment but I can't do it here. I
wanted to send it to (e-mail address removed) but i was not sure that i am
alowed or not. But i just write you the field on one table. If it helps
this way.
The database name is "Test Data".
The tables name are those that i wrote for you previously
And those tables have the same structure with slightly difference in their
fields. But most of them are the same.
For example the structure of one table like Strerile water table contains
the following fields:
Test lot# ( unique )
Prepared by
Date of Preparation
Document# ( unique for all sterile water)
Specific Reference ( unique for all sterile water)
Analyst
Test Date (Medium format, eg. 23-Sep-04 )
Run#
pH value
Concentrate value
Test Result ( I made it automatically returns Pass if both pH value and
Concentrate value are in passing range - special thanks to you guys that help
me on this)
pH meter
Pipettor
calibrator

The point is for every tests we need all these information for QA in a nice
form that i created. We do report on only few fields. ( test lot# , test
Date, pH value, Concent value, and test result ). It may be some values that
is repeated in differnt tables but we need them this way.

 

[Guest]

Hi,
I sent you a description of my tables on Friday 8/5. I havn't gotten the
respond back. Is it duable to make union between tables? is it doable?