Cumulative Frequency

[Guest]

As a fairly inexperienced Access user, I am sure there is a simple anser to
this. Some guidance from teh experts would be really appreciated.

I am trying to produce a cumulative frequency graph of the total number of
instances of an event through time. So far I can't get beyond a total for
each individual date.

tbl_event contains all possible events
tbl_site contains all sites

both are linked to

tbl_master which also contains the date

Required resullt: A chart (or query) that will produce a running total of
the number of events that have occured to date at each site.

Happy to provide more info if required.

Thanks

 

[Guest]

Need a little more data. You did not say which table or combination of
tables that have the events/date/site.

 

[Guest]

It sounds like you need a typical running sum query. You can accomplish this
with a nested subquery. This example is simplified and doesn't include any
joins that you will need, but it will give you the idea.

Select a.FirstField, a.SecondField, (Select Count(b.SecondField) From
MyTable b WHERE b.Date <= a.Date) As RunningCountOfSecond From MyTable a;

The inner query grabs the count of records in the SecondField where the Date
field is earlier than the Date of the current record. If you want a sum
instead of a count, relace the word Count with Sum.

Barry

 

[Guest]

Thanks Karl and Barry for your responses.

Karl-
tbl_event contains a list of event types and details, with unique identifiers.
tbl_site contains a list of location, associated details, with unique
identifiers.
tbl_master conatins the records of individual events, is linked to tbl_event
and tbl_site for that information, and contains an additional field for date
entry.

Hope that helps.

Barry-
The running sum query that you suggested looks very promising. But after
playing around with this concept all weekend I am now feeling a little stupid
and very confused. My lack of experience with Access is a real stumbling
block here.

Can you point me in the direction of more information on constructing this
quiery properly. Or better yet, is there a working example (perhaps in
Northwind?) that I could take a close look at.

Thanks both of you for your time.

 

[Guest]

This can be a bit confusing, especially when you have a join in your query.

Paste the following sqlinto a new query in Northwind and then switch it to
design view. It is a modification of the Sales By Year query. The CumTotal
column is basically a duplicate of the main query's join with the addition of
a where clause that sums all the records that have an date earlier than the
main query's record. Also notice that I had to alias the Orders table in the
subquery as "ords" so the query could understand which reference to the
Orders table I'm pointing to.

SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal,
Format([ShippedDate],"yyyy") AS [Year], (Select SUM(Subtotal) FROM [Orders]
ords INNER JOIN [Order Subtotals] ON ords.OrderID = [Order Subtotals].OrderID
WHERE ords.[ShippedDate]<=Orders.[ShippedDate]) AS CumTotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order
Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null))
ORDER BY Orders.ShippedDate;

Barry

 

[Guest]

Its taken me a while to get to grips with this, but this solution was just
what I was looking for.

My first problem was that my database stucture is slightly more complicated
than the example I gave previously, so getting all the elements to work
properly took some playing with. Managed to sort that out though.

The problem that really stumped me for a while was displaying a pivot chart
of the data correctly. I designed a chart which displayed perfectly when
viewed as a separate form. But when inserted into a grouped report would
display unexpected data. I eventually got around this by building a query
which referenced the CumFreq query, and contained all the fields from this
query (esentially just a direct copy of the CumFreq query, no expressions,
functions or links at all). I'm not sure that I know enough about the way
Access handles things to know why this worked, but it did. I'd be interested
if anyone has an explanation.

Anyway, thanks Barry for your suggestions and help with formulating the sql.

This can be a bit confusing, especially when you have a join in your query.

Paste the following sqlinto a new query in Northwind and then switch it to
design view. It is a modification of the Sales By Year query. The CumTotal
column is basically a duplicate of the main query's join with the addition of
a where clause that sums all the records that have an date earlier than the
main query's record. Also notice that I had to alias the Orders table in the
subquery as "ords" so the query could understand which reference to the
Orders table I'm pointing to.

SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal,
Format([ShippedDate],"yyyy") AS [Year], (Select SUM(Subtotal) FROM [Orders]
ords INNER JOIN [Order Subtotals] ON ords.OrderID = [Order Subtotals].OrderID
WHERE ords.[ShippedDate]<=Orders.[ShippedDate]) AS CumTotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order
Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null))
ORDER BY Orders.ShippedDate;

Barry

Thanks Karl and Barry for your responses.

Karl-
tbl_event contains a list of event types and details, with unique identifiers.
tbl_site contains a list of location, associated details, with unique
identifiers.
tbl_master conatins the records of individual events, is linked to tbl_event
and tbl_site for that information, and contains an additional field for date
entry.

Hope that helps.

Barry-
The running sum query that you suggested looks very promising. But after
playing around with this concept all weekend I am now feeling a little stupid
and very confused. My lack of experience with Access is a real stumbling
block here.

Can you point me in the direction of more information on constructing this
quiery properly. Or better yet, is there a working example (perhaps in
Northwind?) that I could take a close look at.