2 min Help Needed very simple 1-Table 1-Query

[TxSteve]

I haven't touched Access in a long time and can't seem to get started.

I have the following report to produce. See attachment image. I'm pulling the data from a list in SharePoint.

My table is called Assignments and I have the following fields;
Category = "Legislative Communications", "Media Request", "Open Records
Create = Date the task was created (need today's date including anything within the past 7 days.)
TaskStaus = "Not Started","In Progress", "Complete"

Please help.

 

[Trevor G]

I haven't touched Access in a long time and can't seem to get started.

I have the following report to produce. See attachment image. I'm pulling the data from a list in SharePoint.

My table is called Assignments and I have the following fields;
Category = "Legislative Communications", "Media Request", "Open Records
Create = Date the task was created (need today's date including anything within the past 7 days.)
TaskStaus = "Not Started","In Progress", "Complete"

Please help.

You would need to explore using queries, and look at using expression and inparticular for dates date diff. You could use a default field to add the date when a new record is added The default would be CDate(), try not to use CNow() as this adds time as well as a date.

 

[TxSteve]

Here is my SQL view.

I get the first couple of fields to work, but it is not filtering the middle column to only count "complete" tasks

SELECT DISTINCTROW Assignments.[Category], Count(Assignments.Created) AS CountOfCreated, Count(Assignments.TaskStatus) AS CountOfTaskStatus
FROM Assignments
GROUP BY Assignments.[Category]
HAVING (((Assignments.[Category])="Legislative Communications")) OR (((Assignments.[Category])="Media Request")) OR (((Assignments.[Category])="Open Records Request")) OR (((Count(Assignments.Created))=Date()-7)) OR (((Count(Assignments.TaskStatus))="Completed "));

 

[Trevor G]

Could you extract a small amount of data to a new database with the query you have structured so far and upload to the thread then I (someone) will take a look at it for you.

 

[TxSteve]

Please see attached.

 

[Trevor G]

I think what you may need is a cross tab query, if you use the query wizard and then select the fields etc you will start to build a picture. I have indicated below the SQL view from a CrossTab Query based on your sample.

TRANSFORM Count(Assignments.[ID]) AS CountOfID
SELECT Assignments.[Category], Count(Assignments.[ID]) AS [Total Of ID]
FROM Assignments
GROUP BY Assignments.[Category]
PIVOT Assignments.[TaskStatus];

If you copy this into a new query you will see what I am trying to achieve I think this is the way forward, you can get some fairly comprehensive help on this topic.