Count/Total recurring numbers

[mestst64]

I have Datein and Dateout fields with many rows of dates beneath each. I used DatePart in a query to convert each date to a week number; but now need to count, and total, how many times each week number occurs under each field.

I'm sure this can be done easily, but I'm new to expressions, functions, etc, and would greatly appreciate some help!

 

[Pat Hartman]

Create a new query. Select the fields you want to group by and the field you need to sum or count. Press the sigma button on the tool bar. This adds a row to the grid with the words "group by" for each field. Change the "group by" to Sum or Count or whatever for the fields you want to aggregate. This is referred to as a "totals" query.

 

[mestst64]

Thanks Pat,

Side by side, the rows of the Datein and Dateout fields (columns) are not an equal match in terms of either date (now converted to week numbers) or number of rows for either field. A very abbreviated example is as follows:

Datein Dateout
28 28
28 28
28 29
29 29
30 29
30 30
31 30
31
32
etc....

Because running a query with this mismatch resulted in blank cells in either one column or the other (depending on which one was sorted), I tried a separate query for each, and was able to do a Group By for a single week number as well as a corresponding Count of how many times that number occurred - For example: I did a query by Datein only, Grouped by the number 50, and found a count of 11 for the number of rows it appeared in.

NOW....is there a way I can Group By multiple values (ie - 28, 29, 30, etc.) under a single Datein field query - or do I have to do a separate count for each grouping of a particular week number?

THEN.... is there a way I can take the total count of Datein's for each week, and subtract the total count of Dateout's for that same week, so that I can determine what the backlog (if any) is for each week so I can plot those on a chart?

Hopefully my explanation isn't too cryptic.

Thanks for your first response Pat, and any further help is welcomed!

(will return in the morning)

 

[Pat Hartman]

I can't quite picture what you are trying to do. You might try creating two crosstabs, one for DateIn and one for DateOut, and then joining the crosstabs.

 

[mestst64]

Hope this will clarify:

My objective is to create a 3-line chart based on my Access data. One line for Incoming items, one line for Outgoing items, and since the Incoming outpaces the Outgoing, a third line to show the Backlog of items waiting to go out. This chart will show totals for the three lines at weekly intervals.

First I did a query to convert all Incoming and Outgoing dates into week numbers for the weeks in which they occurred. This yielded (in Form view) a solid column of (ascending) Incoming week numbers, next to a column of Outgoing week numbers that had many blank spaces mixed in. When I tried to sort both in ascending order, it got rid of the Outgoing blanks, but I lost data from the Incoming column.

So, I did two individual queries off that query, to separate the Incoming from the Outgoing. This gave me two columns (one in each query) of week numbers with no missing data and no blank spaces.

Because I originally converted each date into a week number, both columns have recurring week numbers in them.

For example: There were four Incoming items on dates during week 48 of last year, so the number 48 is repeated in four consecutive rows under the column in the Incoming query. There were two Outgoing items during that same week, so the number 48 is repeated twice in the Outgoing query. And so on....

Then, I did a Find Duplicates query on each of the separated queries, which yielded totals for the number of times each week number appeared. So now I've got something like the following:

Incoming Query:
Week Number ---- Total
48 --------------- 4
49 --------------- 3
50 --------------- 11
etc.

Outgoing Query:
Week Number ---- Total
48 --------------- 2
49 --------------- 5
50 --------------- 5
etc.

How can I take these results and plot them on a line chart to show those weekly totals (each week number being a unit on the X axis, and the totals on the Y axis) with one line for Incoming, one for Outgoing, and a third which would show the Backlog? (I need to know how to arrive at that Backlog line too).

Hopefully that gives a better picture of where I'm at and what I want to achieve.

Additional help from Pat or anyone would be great! Thanks!

 

[Pat Hartman]

You should be able to create a crosstab that produces:

Week Number ---- Incoming ---Outgoing ---- Total
48 --------------- 4 ---------- 2 ----------- 6
49 --------------- 3 ---------- 5 ----------- 8
50 --------------- 11 --------- 5 ----------- 26
etc.

Will that work for you?

 

[mestst64]

Thanks Pat. This helped me to get the totals into one query. I've now encountered another problem with the last/first week crossing over two different years, so am going to post another thread to address that----

Thanks again