UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query To Show Records For Each Day Of The Year, Access 2016    
 
   
airdata
post Dec 3 2019, 11:29 PM
Post#1



Posts: 45
Joined: 24-October 09



Hi all

I have a query which retrieves the number of records created on a given date using Month([PhotoDate]) and Day([PhotoDate]) from three different tables.
Is there a way to build a query to show resulting data from each of the three tables and for each date, like the layout below. I am thinking a crosstab query but I'm not sure where to start with this.

Date Table1 Table2 Table3
01 Jan 10 8 0
02 Jan 88 65 2
03 Jan 66 95 1
....

I'd like to show all dates 01 Jan through to 31 Dec as row headings. The figures in each of the above columns are the number of records created in the respective table on the given date (regardless of year).

Any help will greatly appreciated.

Thanks

Mark
Go to the top of the page
 
projecttoday
post Dec 4 2019, 12:10 AM
Post#2


UtterAccess VIP
Posts: 11,283
Joined: 10-February 04
From: South Charleston, WV


You have 2 tables with records each of which has Photodate and and an ID field (among others, I assume). Make a query:

SELECT Table1ID, Photodate, "Table1" AS TABLENAME
FROM Table1
UNION ALL
SELECT Table2ID, Photodate, "Table2" AS TABLENAME
FROM Table2
UNION ALL SELECT Table3ID, Photodate, "Table3" AS TABLENAME
FROM Table3;

Save that query. Call up the crosstab query wizard and make a crosstab of the above query. Use the Photodate as the row heading, the tablename as the column heading, and tell it you want a count of the Id field in Table1.

--------------------
Robert Crouser
Go to the top of the page
 
airdata
post Dec 4 2019, 12:34 AM
Post#3



Posts: 45
Joined: 24-October 09



Thanks for the quick response.

The suggested union and query works but it does not eliminate the year. I modified it to the below and the result is close.

SELECT Month([tblSighting.SightingDate]), Day([tblSighting.SightingDate]), tblSighting.SEQ, "tblSighting" as SIGHTINGS
FROM tblSighting
UNION ALL
SELECT Month([tblPhotos.PhotoDate]), Day([tblPhotos.PhotoDate]), tblPhotos.ImageRef, "tblPhotos" as PHOTOS
FROM tblPhotos
UNION ALL SELECT Month([tblFlightLogbook.FlightDateLocal]), Day([tblFlightLogbook.FlightDateLocal]), tblFlightLogbook.SEQ, "tblFlightLogbook" as FLIGHTS
FROM tblFlightLogbook;

Below is the result from the crosstab query (sorry for the alignment):
Expr1000 is the month, Expr1001 is the Day, Total Of SEQ is not required.

Expr1000 Expr1001 Total Of SEQ tblFlightLogbook tblPhotos tblSighting
1 1 87 3 20 64
1 2 247 67 180
1 3 93 35 58
1 4 53 3 39 11
1 5 44 12 32


I'd like to combine Expr1000 and Expr1001 as dd-mmm format and remove the "Total of SEQ".

Any help much appreciated as always.

Thanks
This post has been edited by airdata: Dec 4 2019, 12:49 AM
Go to the top of the page
 
projecttoday
post Dec 4 2019, 12:46 AM
Post#4


UtterAccess VIP
Posts: 11,283
Joined: 10-February 04
From: South Charleston, WV


You will have to swap out the full date in the crosstab with the month-day. You could also do it in the first query.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:28 AM