Quarterly Statistics Query (1 Viewer)

[Mrtgstyles]

Hi All,

So I'm really new to all this, but am totally enjoying my Database experience so far...

I've collated a load of data for my boss where each unique ID has had some form of intervention served on them at some point in the last 3 years. (See example data below with real database fieldnames) My job is to try and produce a quarterly breakdown of what's been going on in the last 3 years. (We work in the financial year so Q1 is 01/04/22-30/06/22)

1) I need to produce a query that can break down how many interventions were served each quarter (e.g. 01/07/2022 - 30/09/2022) and group it by the Age Group.

2) Then I need to produce a query that can show how many interventions were served the previous quarter (e.g. 01/04/2022 - 30/06/2022) but had NO FURTHER interventions served after it. EXAMPLE if an intervention was served on 03/05/2022, but no further interventions (i.e. that post date 03/05/2022) in that record show up, that should be counted. This should also be grouped by Age Group.

I've been mucking around with Count and trying my luck, but I thought it better to learn from the pros in this forum. Also tried it in Excel using SUMPRODUCT, but still cannot get it exactly right. Any help would be gladly recieved.

Kind regards

T

 

[Ranman256]

make a form that has the text boxes to set your date range:
txtStartDate, txtEndDate.
and
txtPrevStartDate, txtPrevEndDate.

make 2 queries to pull the source data range, qsDataDateRng & qsDataPrevDateRng
qsDataDateRng=
select * from table where [dateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

qsDataPrevDateRng will use the other 2 date fields.

Now that you have your base data , build queries off of these to analyze Age Groups (or other metrics)
You can also make a query by putting both qsDataDateRng & qsDataPrevDateRng together to show changes.

 

[ebs17]

I positively assume that the date values to be considered occur in exactly one field of your master data table.

For such considerations, it is helpful to create a calendar table as an auxiliary table. This receives a continuous date for a sufficient period of time as the primary key. Formats of this date calculated according to requirements are stored in other fields, for example the financial year and the quarter. These fields are also indexed.

Now you can link the data table with the calendar table in a query using the date. The date field in the data table should of course also be indexed. This gives you immediate access to the quarter and you can also group the age groups across this quarter and thus count data records. With one data record per quarter, there would be no follow-up process. A well-planned calendar table can then be used for many other measures, and the effort involved in creating one is always worthwhile.

 

[mike60smart]

Hi All,

So I'm really new to all this, but am totally enjoying my Database experience so far...

I've collated a load of data for my boss where each unique ID has had some form of intervention served on them at some point in the last 3 years. (See example data below with real database fieldnames) My job is to try and produce a quarterly breakdown of what's been going on in the last 3 years. (We work in the financial year so Q1 is 01/04/22-30/06/22)

1) I need to produce a query that can break down how many interventions were served each quarter (e.g. 01/07/2022 - 30/09/2022) and group it by the Age Group.

2) Then I need to produce a query that can show how many interventions were served the previous quarter (e.g. 01/04/2022 - 30/06/2022) but had NO FURTHER interventions served after it. EXAMPLE if an intervention was served on 03/05/2022, but no further interventions (i.e. that post date 03/05/2022) in that record show up, that should be counted. This should also be grouped by Age Group.

I've been mucking around with Count and trying my luck, but I thought it better to learn from the pros in this forum. Also tried it in Excel using SUMPRODUCT, but still cannot get it exactly right. Any help would be gladly recieved.

Kind regards

T

The example data looks like it is not normalised?

 

[Mrtgstyles]

The example data looks like it is not normalised?

Hi All,

Thank you for your responses so far. I'm working on building a calendar table now and the forms and queries suggested. I'm not entirely sure what normalised data means, but will look that up now to make sure I understand it.

Thanks for such speedy responses!

 

[Gasman]

Look at some of these threads.
They will have a link that @jdraw posts for reading up on normalization.

Search results for query: normalization

www.access-programmers.co.uk