Performing multiple conditions on data...

[Bec_FS]

I am a beginning-to-intermediate user of Access, I recently ran this query
(QBE), but I often wonder if there was a better way of approaching this…maybe
through SQL? I mainly use the QBE, but I often wonder if I am limiting
myself. First of all, the data I am working with has two tables that are set
up with a one-to-many relationship. (Hindsight, they should have probably
been set up using a many-to-many relationship, with a link table, but that is
not the way it is set up. (I did not predict the movement I got with these
animals). Here are some examples of the two tables, with some sample data.

Table one: tblDaySiteSurveys
SystemNo SiteID SurveyDate AirTemp TimeStart TimeEnd
1 PTH 6/3/2003 3:30:00 PM
137 RMM 6/3/2003 15.2 11:36:00 AM 12:06:00 PM
2 CAS 6/4/2003 3:46:00 PM 4:18:00 PM
3 GSP 6/4/2003 4:31:00 PM 4:40:00 PM
4 ECH 6/4/2003 11:30:00 AM 1:02:00 PM
134 MOS 6/5/2003 11.3 11:47:00 AM 1:27:00 PM
136 VMC 6/5/2003 2:00:00 PM
131 CLM 6/5/2003 11.3 11:47:00 AM 1:27:00 PM
132 UVM 6/5/2003 11.3 11:47:00 AM 1:27:00 PM
133 MVM 6/5/2003 11.3 11:47:00 AM 1:27:00 PM
8 SMP 6/18/2005 12.5 12:13:00 AM 3:15:00 PM

Table two: tblAnimalCaptures (Thousands of Records)
UniqueCaptureId SystemNo CaptureTime Recapture PitTagNumber GrossWeight_g Sex AgeClass
1 4 11:30 N 4411234567 30 F A
2 4 11:30 N 4412345678 29.2 M A
3 4 11:30 N 4413456789 22.9 M A
4 4 11:35 N 4414567891 22 M A
5 4 11:40 N 4415678910 23.1 M A
6 4 11:40 N 4416789101 25.7 M A
7 4 11:49 N 4417891011 21 M A
8 4 11:52 N 4418910111 44.9 F A
9 4 12:47 N 4419101112 20 M A
10 4 12:50 N 4411011121 20.1 M A
11 4 13:02 N 4411112131 22.1 M A
12 8 12:55 Y 4412345678 32 M A
13 8 13:22 Y 4414567891 23 M A
14 8 13:25 N 4411213145 33 F J
Etc.

As you can see each day we go out, we can get multiple captures, each survey
data get a unique record in tblDaySiteSurveys, and each capture during that
day gets a uniqueCaptureId. Of course a PitTagged animal can get captured
multiple days, but the animals are actually traveling to other sites as well
(unexpected).
So, what I wanted to ask of this data, was give me all the captures (Unique
pit-tags) at say SiteID “SMP†and of the those captures, what other sites did
that pit tag exist in? So in the QBE first did a query with the two tables
linked and asked it to give me site SMP, and unique PitTags. Then I used
that query, in another query linking the pittags I got from the first query,
linked to pittags in the regular table, and then I ask it to give me SiteId.
A two step process, but I got my answer, however…I am going to do this type
of query a lot and would love to know a better way of approaching this. I
would even like to know the pittags from SMP that were not in any other
location. Etc. Hope this all makes sense. Any advice would be well
received, I would definitely like to expand my horizons. Thank You!

P.S. Should I possibly restructure these table to make them many-to-many?

 

[Michel Walsh]

maybe something like:



TRANSFORM COUNT(*) AS theCell
SELECT pitTagNumber, COUNT(theCell) As TheCount
FROM Surveys INNER JOIN Captures
ON Surveys.SystemNo = Captures.SystemNo
GROUP BY pitTagNumber
PIVOT SiteID



so, vertically, you will get one record per pitTagNumber number,
horizontally, you will get the total number of sites the pitTagNumber has
been seen, in TheCount
and also, how many times it has been seen in each site, under the siteId
column.




Hoping it may help,
Vanderghast, Access MVP

 

[Bec_FS]

Thank you for your response! I did run a CTquery similar to this one, but in
order for me to find out the pittags that were unique to SMP I would have to
first say where that cell was not null. And then from there, I would have to
put in each cell where SiteId not null in order for it to give me the answer
that I want, so I went the other route. One thing is I need a SiteId "base"
to start with. For example, I want all the Pit Tags that were at SiteID
"SMP". Then it would give a list of SiteId and PitTag, from that list of
PitTags, I would like it to give me the other sites that those existed in.
So when I ran the QBE, I first had to get the pit tags that were unique to
just SMP, then I ran another query that joined that pittag with the pittag in
tblAnimalCapture (only including the pittags where the table and query were
equal). I am thinking of it somewhat like this...If SiteId "SMP" is not
null, then give me list of PIT TAGS, then show me other siteId(s) that those
specific "SMP" pittags were also.

P.S. It seems that your syntax does not address the full question, unless I
am misunderstanding your syntax...which I very well could be!

 

[Michel Walsh]

SELECT DISTINCT c.SiteID
FROM ((SELECT * FROM Surveys WHERE SiteID="SMP") AS a
INNER JOIN Captures As b ON a.siteID=b.siteID )
INNER JOIN Captures AS c ON c.pitTagNumber=b.pitTagNumber


Hoping it may help,
Vanderghast, Access MVP