Help with MS Access query

I need a query that delineates new records that have been added for each month.  The records have a unique ID.  The update month is also a field. Please see the example data database and the desired results (excel)

Any Takers!
TestMDB.accdb
Example.xlsx
Thomask23Asked:
Who is Participating?
 
BitsqueezerConnect With a Mentor Commented:
Hi,

that was not clear from your example and to be exact, it is not clear from what you wrote now.

You can create a query like this to get the newest ID of each date if that is what you want:

SELECT [Sample Table].[Update Date], Max([Sample Table].ID) AS MaxvonID
FROM [Sample Table]
GROUP BY [Sample Table].[Update Date];

Open in new window


You see that any date value always only have one ID and so the result of a count in a further query would always be 1.

(That can also be transformed like in the example above but normally it's better to create normal queries than crosstab queries as these have a limitation of 255 columns.)

If that's not what you thought of then please be more exact.

A "new ID" cannot be "updated", only inserted.

Cheers,

Christian
0
 
BitsqueezerCommented:
Hi,

maybe this query is what you want:

TRANSFORM Count([Sample Table].[ID]) AS AnzahlvonID
SELECT 'Count Of Dates' AS [Count of Dates], Count([Sample Table].[ID]) AS [Count of all]
FROM [Sample Table]
GROUP BY 'Count Of Dates'
PIVOT Format([Update Date],"Short Date");

Open in new window


You can of course also group that by "Data":

TRANSFORM Count([Sample Table].ID) AS AnzahlvonID
SELECT [Sample Table].Data, Count([Sample Table].ID) AS [Count of all]
FROM [Sample Table]
GROUP BY [Sample Table].Data
PIVOT Format([Update Date],"Short Date");

Open in new window


Cheers,

Christian
0
 
Thomask23Author Commented:
Almost...

The records are identified as new by the ID. If a new id is updated to the data it should only be count it. I want to count the only the new records added by the updated date.
0
 
Thomask23Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.