Map fortnightly data to months - can it be done?

[Rob Parker]

Hi All,

I've puzzled over whether I can accomplish what I want using just a query
(or a set of queries), and came to the conclusion that I couldn't - but that
doesn't mean it can't be done.

I'm trying to map a set of fortnightly salary data into months in a
financial year. I have the following tables and fields:

tblStaffEffort
FY - integer
Period - integer (number from 1 to 27 - only gets to 27 about one year
in 11)
Hours - double
Cost - currency
...

tblMDS (monthly datasets)
FY - integer
Mnth - integer (1-12, for Jan to Dec)
Yr - integer (since our FY runs July to June)
MaxPeriod - integer (the maximum period number for the year/month)
...

I've actually solved my problem by writing code to loop through two
recordsets based on each of these tables, and write the data into a
temporary table which I can base my queries/reports on. But I'm curious as
to whether it's possible to do this with a query/sub-query(s). Any takers?

TIA,

Rob

 

[vanderghast]

I suspect it can be done, but since you did not said what has to be done,
exactly, that is quite difficult to come with a solution for your specific
case.

I would start with a total query on the first table. You have tried that? it
may be all what you need, and be so ... simple... that it appeared to be too
simple to be the solution you need, while there is not reason to be more
much more complex than that, really.

But again, you did not give much details.


Vanderghast, Access MVP

 

[Daryl S]

Rob -

Can you set up a conversion table that has the FY and Period in it as the
primary key, and has additional columns for the Month and Year? Then you can
use a join to convert this in any query.

 

[KARL DEWEY]

I'm trying to map a set of fortnightly salary data into months in a
financial year.
Can you post sample data from the tables and what you want the results to
look like?

 

[Rob Parker]

I thought I did say exactly what I wanted to do - map (translate/convert) a
set of data based on fortnightly periods in a set of data based on months.
Here's some sample data to show what I've got and what I want.

tblStaffEffort:
FY Period Hours Cost
2009 1 12 480
2009 2 10 350
2009 3 11 400
2009 4 10 380
2009 5 13 390
2009 6 15 450
2009 7 10 350
2009 8 12 450
2009 9 14 550
2009 10 13 390
2009 11 10 350
2009 12 11 400
2009 13 11 400
2009 14 10 350
2009 15 14 420
....

tblMDS: (I've included an additional field (Dataset) which I didn't mention
before, which allows the monthly dataset data to be sorted into
time-sequential order).
Dataset FY Yr Mnth MaxWTEPeriod
13 2009 2008 7 2
14 2009 2008 8 4
15 2009 2008 9 6
16 2009 2008 10 9
17 2009 2008 11 11
18 2009 2008 12 13
19 2009 2009 1 15
20 2009 2009 2 17
21 2009 2009 3 19
22 2009 2009 4 22
23 2009 2009 5 24
24 2009 2009 6 26

Note that for datasets 16 and 22 there are three fortnightly periods within
the month.

This is what I want:
FY Yr Mnth Hours Cost
2009 2008 7 22 830
2009 2008 8 21 780
2009 2008 9 28 840
2009 2008 10 36 1350
2009 2008 11 23 740
2009 2008 12 22 800
2009 2009 1 24 770
....

Hope this makes it clear.

BTW, a total query on the first table will not give me what I want, since it
does not have a month field.

Rob

 

[Rob Parker]

Thanks for the suggestion Daryl,

It's a possible solution to the problem, but not one I'm wanting to
implement (particularly as I've got a working solution with my code). But
it's missing the point of my question, which was "can it be done with a
query(s) using my existing tables".

Rob

 

[Rob Parker]

See reply to posting by Vanderghast.

Rob

financial year.
Can you post sample data from the tables and what you want the
results to look like?

 

[John W. Vinson]

Thanks for the suggestion Daryl,

It's a possible solution to the problem, but not one I'm wanting to
implement (particularly as I've got a working solution with my code). But
it's missing the point of my question, which was "can it be done with a
query(s) using my existing tables".

Not unless there is some rocksolid algorithm or lookup table to translate
payperiod 13 to a specific date range. Is there?

 

[Rob Parker]

No, there's not. The only data that exists (relating period to month) is
the maximum period in the month - and that changes each year. The date
range (day/month) of each pay period changes each year.

Seems like my code solution was a good choice, otherwise I'd still be trying
to build a query which can't be done ;-)

Thanks,

Rob

 

[John W. Vinson]

No, there's not. The only data that exists (relating period to month) is
the maximum period in the month - and that changes each year. The date
range (day/month) of each pay period changes each year.

Seems like my code solution was a good choice, otherwise I'd still be trying
to build a query which can't be done ;-)

I'm sure there's a better code solution than looping through two recordsets,
but - given that I have no way to know how the pay periods are defined (by
what you say they could be arbitrary at the financial VP's whim every year), I
can't suggest one. Is there *ANY* rationale to the assignment of pay period
numbers? E.g. are they every two weeks, year in year out? If so a sufficiently
clever DateDiff expression could solve it.

 

[Rob Parker]

The pay periods are not arbitrary; they are every two weeks, year in year
out. That's why they move in relation to fixed dates. And there's no date
data in the salary data (imported from another system); however, I could get
it if required, and populate a table with it. That would allow me to use
the approach Daryl suggested.

But for now, I'll stick with my code solution - it runs quickly (outer loop
12 months, inner loop up to 26/27 periods) because I'm doing this on a
totals query from the original data, so there's only a single record for
each period. If I was needing to do it on the raw data (thousands of
records), it might be a different story ;-)

Rob

 

[vanderghast]

If the only missing information, from the total query, is the month matching
the period, make a second query: bring the query making the total AND the
table supplying the month given the period number; join the query to the
table trough the period value, and viola, drag the required fields from the
total query and the month field from the said table, onto the lower part
(grid) of the query designer.


Vanderghast, Access MVP

 

[Rob Parker]

Thanks Vanderghast.

I understand what you're saying. My problem with the tables/fields as I
have them, is that there is not a translation/lookup table that I can join
on - the only data I have relating period to month is the MaxWTEPeriod field
in tblMDS.

If you've been following other branches of this thread, you'll have seen
that John Vinson thinks it's not do-able by query given the existing
datasets that I have. And that was really the essence of my question - as I
said in my original post, I do have a solution to the problem.

Rob

 

[vanderghast]

If you don't use data from other source, and you said

"I've actually solved my problem by writing code to loop through two
recordsets based on each of these tables",

the relevant data seems to be present, but indeed, if your solution use some
additional data that is not in the tables, then you cannot solve the problem
with SQL unless you give it access to that data.


Vanderghast, Access MVP