How to run a query to sort by id group by location and date and count by date

[jshark]

Hi,
I am new to both access (working in access 2003) and this forum so I hope that my questions are well phrased and suitable. I am always grateful for any advice that can be offered, this forum has already been a fantastic resource.

My challenge is as follows. I have two tables

Vemco passive tags – [Vue tag ID] and [True/False] toggle button
Vue2database – [Date and Time], [Station Name] and [Transmitter]

The first table enables me to select the individual tags I am interested in. The second table is a record of every time a tag has been recorded at an automated recording station.

So far I am able to run a query that lets me know how many times a tag has been recorded at a specific station, but I need this count grouped in order of visit.

So a table might look like:
Tag Station First seen Last seen Count
3394 LHA12 12/1/09 12/2/09 4
3394 LHA14 12/5/09 12/6/09 2
3394 LHA12 12/9/09 12/10/09 10

This then tells me that the tag was at LHA12 between the two dates four times then moved to LHA14 then back LHA12.

I hope this make sense if not I can add more info as needed.

Thanks again to you all for your help.

J

[dsatino]

You can set the sort properties in the design window of the query. Set the sort of both the tag and First seen to 'ascending' and clear all other sorts if necessary.

[jshark]

Thanks dsatino this gets me half way there. I still need to be able to count the number of times a tag is recorded during each visit. the problem, i have when i use the COUNT function is that is counts all records in the dataset not just during that visit. I hope this makes sense. thanks again

[Oralloy]

Try something like:

SELECT tag.[Vue tag ID] AS "Tag",
       vue2.[Station Name] AS "Station",
       MIN(vue2.[Date and Time]) AS "First seen",
       MAX(vue2.[Date and Time]) AS "Last seen",
       COUNT(vue2.[Transmitter]) AS "Count"
  FROM [Vemco passive tags] tag,
       [Vue2database] vue2
  WHERE (tag.[Vue tag ID] = vue2.[Transmitter])
  ORDER BY tag.[Vue tag ID], vue2.[Date and Time], vue2.[Station Name]
  GROUP BY tag.[Vue tag ID], vue2.[Station Name]

[jshark]

Thanks Oralloy,
I have tried what you suggested but am having a little problem when i try to run the query as i get "Syntax error (missin operator in query expression 'vue2.[station Name] GROUP BY tag.[vue tag ID]'. Any thoughts on what i am doing wrong?

thanks again for your help.

SELECT [Vemco passive tags].[Vue tag ID] AS "Tag", 
       Vue2database.[Station Name] AS "Station", 
       MIN(Vue2database.[Date and Time]) AS "First seen", 
       MAX(Vue2database.[Date and Time]) AS "Last seen", 
       COUNT(Vue2database.[Transmitter]) AS "Count" 
FROM [Vemco passive tags] tag, [Vue2database] vue2 
WHERE (tag.[Vue tag ID] = vue2.[Transmitter]) 
ORDER BY tag.[Vue tag ID], vue2.[Date and Time], vue2.[Station Name]
GROUP BY tag.[Vue tag ID], vue2.[Station Name];

[Oralloy]

@jshark,

Unfortunately no, but you might try composing the query a little bit at a time. All I did was think about the problem and pull an answer out of the ether (where uncle Sigmund says I retain things). I really don't have the ability to test easily, short of constructing a database and fooling with it.

First try the single table select:

SELECT tag.[Vue tag ID] AS "Tag"
  FROM [Vemco passive tags] tag;

Then, if that works, try the unrestrained join:

SELECT tag.[Vue tag ID] AS "Tag", 
       vue2.[Station Name] AS "Station", 
       MIN(vue2.[Date and Time]) AS "First seen", 
       MAX(vue2.[Date and Time]) AS "Last seen"
  FROM [Vemco passive tags] tag, 
       [Vue2database] vue2

And, if that works, add the join condition:

SELECT tag.[Vue tag ID] AS "Tag", 
       vue2.[Station Name] AS "Station", 
       MIN(vue2.[Date and Time]) AS "First seen", 
       MAX(vue2.[Date and Time]) AS "Last seen"
  FROM [Vemco passive tags] tag, 
       [Vue2database] vue2 
  WHERE (tag.[Vue tag ID] = vue2.[Transmitter])

And, if that works, try adding the order by clause:

SELECT [Vemco passive tags].[Vue tag ID] AS "Tag",  
       Vue2database.[Station Name] AS "Station",  
       MIN(Vue2database.[Date and Time]) AS "First seen",  
       MAX(Vue2database.[Date and Time]) AS "Last seen"
  FROM [Vemco passive tags] tag, [Vue2database] vue2  
  WHERE (tag.[Vue tag ID] = vue2.[Transmitter])  
  ORDER BY tag.[Vue tag ID], vue2.[Date and Time], vue2.[Station Name]

Two things I may have fouled up:
   1) The table aliases might need to be enclosed in square brackets.
   2) I really screwed up and got confused on how to use the ORDER BY clause.

Let me know how it works out.

Cheers!

[jshark]

@oralloy,
thanks again for your help. I tried various ways to get this to work but in the end i figured it wasn't actually going to give me the result that i needed so changed strategy in the end. I ended up using the following code, which although not ideal does give me the data i require.

cheers,

jshark

ps the min max formula i am now using in another query so thanks for that.

SELECT Vue2database.Transmitter AS Expr1, Format([Date and Time (UTC)],"YYYY/MM/DD HH") AS [date], Vue2database.[Station Name] AS Expr2, Count(Vue2database.[Station Name]) AS [CountOfStation Name], Vue2database.Latitude AS Expr3, Vue2database.Longitude AS Expr4, [Tag search query].Species, [Tag search query].Gender, [Tag search query].TL
FROM Vue2database, [Tag search query]
GROUP BY Vue2database.Transmitter, Format([Date and Time (UTC)],"YYYY/MM/DD HH"), Vue2database.[Station Name], Vue2database.Latitude, Vue2database.Longitude, [Tag search query].Species, [Tag search query].Gender, [Tag search query].TL
ORDER BY Vue2database.Transmitter, Format([Date and Time (UTC)],"YYYY/MM/DD HH");

[colintis]

The ORDER BY line would be optional to include because GROUP BY also sort the output from its first GROUP BY element in ascending order, unless you want to order by a descending order.

Whenever you implement a function thing such as count, format, etc. GROUP BY clause must also be include in order to make the query work, so give a try on putting back the MIN & MAX and include the name in the GROUP BY clause. :)

[Oralloy]

@jshark, thanks for the feedback.

@colintis, yep, using group by helps, although I thought that the grouping was usually inferred. In jshark's case, though, that might be the missing bit.

Luck!

[dsatino]

Just out of curiousity, are you tracking sharks?

[colintis]

@Oralloy
It is, but somehow we can explain it in more straight forward when implementing functions in SQL: How many things you added to the SELECT clause, will be how many things you will added to the GROUP BY clause.

[Oralloy]

@colintis,

That's a very good way of explaining the GROUP BY clause to newbs. Thank you.

Cheers!