I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.
Right now, this is what I have:
Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992
When I run it, it returns the title "CountOfPurchaseDate" with no value below.
Please help!
6 6908
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.
Right now, this is what I have:
Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992
When I run it, it returns the title "CountOfPurchaseDate" with no value below.
Please help!
What you have there so far should be giving you a count for each time (um... second, I think) for the whole of January 1992. In other words, something like (up to) 86,400 entries for each day, depending on your data.
I think you could try setting up a formatted field, and count that. Something like YYYYMM: Year([Purchase Date]) & Month([Purchase Date])
Than use it much the same way you stated above.
What you have there so far should be giving you a count for each time (um... second, I think) for the whole of January 1992. In other words, something like (up to) 86,400 entries for each day, depending on your data.
I think you could try setting up a formatted field, and count that. Something like YYYYMM: Year([Purchase Date]) & Month([Purchase Date])
Than use it much the same way you stated above.
Sorry, I think I have that a bit garbled.
What I would try first (keeping in mind I'm not an Access expert) is to set up your formatted field made up of the year and month, group by that, and add a second field with a Count (I don't think it matters much what field you use for this part).
NeoPa 32,554
Expert Mod 16PB
If you have SQL then similar to :- - SELECT Count([PurchaseDate]) AS Total
-
FROM [SomeTable]
-
WHERE [PurchaseDate] Between #1/1/1992# And #1/31/1992#
and no records show, that impies you don't have any matching data.
What records do you have in the table that you'd expect to be included?
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format.
Right now, this is what I have:
Field: Purchase Date
Table: SomeTable
Total: Count
Sort: Not Sorted
Show: X
Criteria: Between 1/1/1992 And 1/31/1992
When I run it, it returns the title "CountOfPurchaseDate" with no value below.
Please help!
Try this:
SELECT Month([Purchase Date]), Count(Month([Purchase Date])), Year([Purchase Date]), Count(Year([Purchase Date]))
FROM SomeTable
WHERE Year([Purchase Date]) >= 1992
GROUP BY Month([Purchase Date]), Year([Purchase Date]);
NeoPa 32,554
Expert Mod 16PB
In Access you don't need to specify a GROUP BY clause if the grouping goes across the whole recordset (as restricted by the WHERE clause of course).
Count(PurchaseDate) will count all the non-null occurrences of PurchaseDate which should normally be equivalent to a record count of the specified records.
In Access you don't need to specify a GROUP BY clause if the grouping goes across the whole recordset (as restricted by the WHERE clause of course).
Count(PurchaseDate) will count all the non-null occurrences of PurchaseDate which should normally be equivalent to a record count of the specified records.
Of course, when in doubt, you can just switch the query to a normal Select query and run it to see whether anything matches your criteria. I generally do that with my Delete queries as a final check before running them.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: BlackFireNova |
last post by:
I need to write a report in which one part shows a count of how many
total records fall within the working days (Monday - Friday) inside of a
(prompted) given date range, in a particular...
|
by: FatBoyThin |
last post by:
I love it when I back myself into a corner. Seems to be the fastest way to
learn.
I have 3 tables; tblINBOX, tblOUTBOX and tblPENDING
The relevant rows are as following; Date, userID
userID is...
|
by: Robert |
last post by:
I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How...
|
by: PC Datasheet |
last post by:
Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 ...
|
by: rugger81 |
last post by:
I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am...
|
by: No bother |
last post by:
I have a table which has, among other fields, a date field. I want to
get a count of records where certain criteria are met for, say, three
days in a row. For example:
NumWidgets Date...
|
by: sixdeuce62 |
last post by:
Hello,
I am trying to create a query that will prompt me to enter the
parameter value if beginning date and ending date.
I have created everything I need in the query, but I have to manually
go...
|
by: devyon122 |
last post by:
I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query:
TRANSFORM Count(imagingdb.ID) AS CountOfID
TRANSFORM...
|
by: jambonjamasb |
last post by:
I am wanting to create a report that summarises the number of items within a date range.
For example I have a FIELD called System_Change.
This is a drop down COMBOBOX that uses words like unix,...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |