Include all minutes (1 Viewer)

Dafad · Sep 16, 2015

Hi Experts,
I have a table containing fields for Date, Time, and amount.
I want to create a crosstab query with:
- Date as the column header
- Time as the row header
- Sum of Amount as Value

This is easy enough however, I want the query to show all minutes of the day in Time, regardless of whether any record has this minute of the day. I want these to display as null values.

What's the best way to go about this?

Thanks in advance!

plog · Sep 16, 2015

You can't create records in a query that don't exist in the underlying datasources. That means you need a datasource for every minute of every day you want to report on. Do you have that?

Luckily I know a short cut that will require just one table of every minute of a day (1440 records) instead of every minute of every day you want to report on (1440 records * #ofdays).

Once you have that you can accomplish what you want, but otherwise, no. Also, in your existing table you shouldn't store Date and Time in 2 different fields--its called a Date/Time field for a reason--it can accomodate both at once.

Dafad · Sep 16, 2015

Thank for the reply plog

No I don't have every minute of every day as this is a query I'm going to need to run every day with new data, this data is from an external system which is why I have the date and time in seperate fields, also in this case teh date and time don't technically relate to each other.

Before posting I had tried having a play around with a linked table containing all minutes of the day but had come to the same conclusion that I needed every minute of every day. If you have a shortcut to avoid this then this would be greatly appreciated!

plog · Sep 16, 2015

You can't avoid a table with all the minutes of a day, but you can take a short cut in populating it. Just build a spreadsheet, put "DayMinute" in A1, format the rest of the A column as time, put 12:00 in A2, use a function in A3 to add 1 minute to A2 and then copy that function down 1439 rows. Copy and paste as values and import that into your database.

Dafad · Sep 16, 2015

Ok I have that.

I've tried linking the all minutes table to the original table by the times but I'm getting 'Type mismatch in expression'

Is this what you had in mind?

plog · Sep 16, 2015

Nope. Now you need a list of all days you want to report on. Do you have that?

Give me the name of the Table with all the minutes (include the field name) and the name of the Table/Query with all the dates to report on (include the field name).

Dafad · Sep 16, 2015

no I dont, the list of all days would be a list of all days that exist in the table, is there a way af automating that?

plog · Sep 16, 2015

You would build a query to get those.

SELECT DateFieldName From YourTableName GROUP BY DateFieldName

Include all minutes (1 Viewer)

Dafad

New member

plog

Banishment Pending

Dafad

New member

plog

Banishment Pending

Dafad

New member

plog

Banishment Pending

Dafad

New member

plog

Banishment Pending

Similar threads

Users who are viewing this thread