31 Day Fields + MTD Field; Need to extract a day and compare against MTD (1 Viewer)

[andrewaldrich]

I have a dbase with payroll data for a 31 day period. Each day (1 to 31) is a field in a record and is named as Day1, Day2, Day3 and so on. I have designed a Query that presents each days data, and sums on a MTD basis. I am forced to design my records using this format as I am importing data from a separate system that cannot be reformatted.

I'd like to be able to:

Choose a day and report the data and it's MTD value.

For example, I choose to report payroll for Day8. I need a report to display the current day's data (Day8), and the MTD results (sum Day1 through Day8).

Can this work without creating 31 separate reports?

Thanks - could really use a quick response.

 

[plog]

I am forced to design my records using this format as I am importing data from a separate system that cannot be reformatted.

No, you are forced to import your source data with that structure; you are not forced to store and use your data properly once imported.

You need to structure your database properly, without regard for how its being imported. Once that structure is built, you fill your good structure with data from the bad structure. If this importation will be a recuring event, you need to streamline the process so that its done effeciently.

Do that and the query you want (and the 25 others that you haven't mentioned or thought of yet) will be trivial to generate. Your hands are not tied.

 

[andrewaldrich]

WOW ... that wasn't a ton of help! But did get me thinking ...

Lets try this again - each record contains EE Name, Position Worked, and 31 Days of data as Day1, Day2, Day3, Day4 ... and so on. This data come from a separate Time and Attendance system that I can't extract in a list format, otherwise I'd prefer to have my file as EEName, Position, Day.

Now knowing this, back to my original question - how can I pull just 1 field and compare it against a MTD without having to create 31 different Queries and 31 Different Reports.

 

[spikepl]

Let us indeed try again. Your issue is understood. You now need to actually make an effort and understand the assistance provided by plog.

Just because your external data is in some given format, then it does not mean that you have to live with processing it in the same format. Instead of jumping though hoops, much easier to normalize the data after import and use all the bells and whistles of access as they were designed to be used. SO transform your data after import and your problems go away.

 

[andrewaldrich]

Normalize the data after import ... OK - agreed. So now assuming I am importing the file as described - each line consisting of EE information, plus 31 days of data fields per record, how do you best normalize this in Access?

 

[spikepl]

depends .. it could be done while importing, if the data is read line by line, or afterwards, having 31 append queries defined once and for all , reading from the staging table and appending to the data table..

 

[namliam]

Personally wouldnt have 31 append queries, though its more work I would probably design some code to read the file to prevent bloating of your database... Otherwize you might consider using a linked table/file to do the same.

Also 31 queries is a nightmare... IMNSHO...
Would personally atleast prefer something like:"

Sub run31queries()
    Dim days As Integer
    DoCmd.SetWarnings False
    For days = 1 To 31
        DoCmd.RunSQL " INSERT INTO tblTotal ( ID, EE, DayValue, DayDate ) " & _
                     " SELECT tblStage.ID, tblStage.EE, tblStage.Day" & days & "," & "Do something to create the date" & _
                     " FROM tblStage; "

    Next days
    DoCmd.SetWarnings True
End Sub

Even better would be to store the EE and any other "single" information into a seperate table.

 

[andrewaldrich]

Thanks All! Hadn't thought of the Append Query to normalize the data - hadn't never been faced with the issue of importing cross-tab data - thanks for the help!

 

[andrewaldrich]

Thanks all - problem solved - hadn't considered the Append Query to normalize the cross-tab import of data. Much easier and a whole less troublesome as I work through the creation of the dbase.