Table Structure (1 Viewer)

[kvar]

I have a Db that keeps track of each Departments budgeted hours and relative cost, as well as the actual hours and cost. To calculate variance. I have my tables set up for the actual, this comes in the form of a report through Excel so it's easily imported to a table. It contains Name, week ending date, pay rate, hours worked, etc.
Now what I'm (eventually) trying to do is create a form so each department head can input their budgeted (estimated) hours for each person for each week of the quarter. Then we can figure out how much they were off by in dollars, hours and head count.
The problem I'm having is how to set up the table where this will be stored. Obviously I need fields for the name of the Department, employee name, position number, etc. But they have to input the number of hours by week. So all I can think of is to have a field for each week ending date of the quarter. But that is a LOT of weeks, and a LOT of fields to create.
If I create a field for the week ending date and a field for hours then all 2000 employees will be listed in there 52 times, creating a ton of records.
I'm just not seeing any other ways besides those two.

There must be a better way, any ideas??

 

[llkhoutx]

Draw a scheme of related tables for what you want. Have a row for each employees work week. Pay particular attention to the one-to-many relationship required.

 

[kvar]

That was one of the ways I thought of and mentioned. The problem is then I end up with a table, let's say BudgetedHours, and in that table I have the fields:
Employee WeekEndingDate Hours

Then all 2000 employees have to be listed 52 times. That would create so many records that I'm afraid the queries used to, say sum a Departments hours by employee, would take forever to run.

 

[James Deckert]

100,000 records is not excessive for a query to run. My DB has 400,000 records now and works fine. If you created a field for each week, you'd have fewer records, but your forms/reports and queries would be a nightmare to create.

 

[kvar]

Yeah, I thought that too. I guess the biggest problem is how to import the currently existing budget forms if I create the fields that way. They are currently in Excel spreadsheets with about 100 columns(including the weeks) and contain fields for calculating monthly and weekly totals, grand totals, etc. It's ridiculous. Hence the trying to get rid of it. When I import I'll basically have to write what 52 append queries? Because it will have to add a new record for every week of the year. I can do it in VBA, but still, that's a lot of copying a pasting and just changing what to update the date field too.
It's too late in the day for me to even think about that!

 

[llkhoutx]

Do not include all employee weekly time in one row, that violates a basic rule of database normalcy. Don't worry about query time unless you're doing it with ledger sheets and a pencil, it's insignificant. I've queried millions of Access rows in a very few seconds. Your queries will be instantaneous.

 

[llkhoutx]

It's not uncommon for Excel sheets to have non-normalized data which has to be restructed once imported into Access.

Isn't it obvious that Excel is not a database tool but is better than Access in displaying data. Append queries are very very fast and are easily expensed sequntially with vba, e.g.

sub OnSomeEvent_Onclick()
docmd.hourglass true 'turn hourglasson to indicate something is happending
docmd.setwarnings false 'to skip message saying you're appending record(s) to a table
docmd.openquery "query1"
docmd.openquery "query2"
...
docmd.openquery "queryn"
docmd.setwarnings true 'display all messages
docmd.setwarnings false 'turn off hourglass off to indicate action completed
end sub

 

[kvar]

Obviously I didn't describe the spreadsheet the data is coming from well enough. I've attached a screenshot of one (there are about 20). The columns you can't see to the left are all calculations (things I'll no longer need). And then the dates for the rest of the fiscal year go to the left and right as obviously there are a lot.
So, you can see why an append query doesn't work. Even just an external data import doesn't work. It WILL work if I name a field in the table for every week in the year. But I don't want to do that, that's crazy.
So what I'm left with then is having a record for each person for each week. Which is fine. However to get that data in there, the only way I've come up with to avoid a ton of copy and paste, would be to write the append query using VBA/SQL so that it only brings in the first field/column of hours, then have it update the tables date field (where Is Null) to whatever that first date is. Then do it again with the next column of hours and changing the date to the next week. And so on and so on and so on, basically about until the time I retire. Unless you know a quicker way to get them in there so there is a seperate record for each person for every column with the correct corresponding date?

 

[gemma-the-husky]

if you have tables for actuals, then often the budget values should mirror the actuals, although one set of numbers may be more detailed than the other.

that's the way to work though, I am sure.

now, I've seen the spreadsheet, you probably need to have a think, you should not be storing the data exactly as you see the spreadsheet.

effectively the weekly figures should be stored vertically.

so you get something like this

ref-------week--actual--budget
db1-003 wk 28 xxx
db1-003 wk 29 xxx
db1-003 wk 30 xxx
db1-003 wk 31 xxx
db1-003 wk 32 xxx
db1-004 wk 28 xxx
db1-004 wk 29 xxx
db1-004 wk 30 xxx
db1-004 wk 31 xxx
db1-004 wk 32 xxx
etc

then you can store the budget alongside the actual, if applicable.
the new import only needs to collect/process the next weeks data, but it can be a fiddly exercise getting it all automated. you shouldn't need cut and paste. If you are importing week 32, then load the spreadsheet into a table, and get your query/function to use the data in the column named "week 32", or whatever.

doing it this way will make the whole thing much more efficient.

 

[kvar]

When I receive the report for the actual hours it comes in a spreadsheet that is already listed as one week per row. So its' very easy to do an import and just append the new records. No problem.

But the budgeted hours come as the above spreadsheet attachment indicates. I would love to import them to be stored vertically, one week per record. But I can't think of any way to write an append query from the data I have to make that happen. I have to append the same worksheet over and over again putting in just one week (column) at a time so that each record will show the correct week ending date.

Or am I missing something obvious? If there is a simpler way to get the records in there so there is a seperate record for each week I would love some ideas! That's why I'm here after all.

 

[llkhoutx]

It takes a query for each week, not one query. As I previous indicated, this normalizes you data and is very fast to execute.

 

[HiTechCoach]

Obviously I didn't describe the spreadsheet the data is coming from well enough. I've attached a screenshot of one (there are about 20). The columns you can't see to the left are all calculations (things I'll no longer need). And then the dates for the rest of the fiscal year go to the left and right as obviously there are a lot.
So, you can see why an append query doesn't work. Even just an external data import doesn't work. It WILL work if I name a field in the table for every week in the year. But I don't want to do that, that's crazy.
So what I'm left with then is having a record for each person for each week. Which is fine. However to get that data in there, the only way I've come up with to avoid a ton of copy and paste, would be to write the append query using VBA/SQL so that it only brings in the first field/column of hours, then have it update the tables date field (where Is Null) to whatever that first date is. Then do it again with the next column of hours and changing the date to the next week. And so on and so on and so on, basically about until the time I retire. Unless you know a quicker way to get them in there so there is a seperate record for each person for every column with the correct corresponding date?

The design of your tables should not be dependent on the spreadsheet's de-normalized layout (like a report).

I always import spreadsheet data into a working/temp table. I use append queries and/or VBA code to break down the data into the correct tables.

 

[kvar]

I am pulling it into a temp table first. Then I run an append query that appends the fields I need, but I can only include one of the "hours" fields because I need the "Date" field to equal that date for only those records.
Then the next append query can pull everything but with the 2nd of the "hours" fields, and update the "Date" field to that weeks date. And so on and so on until you're running 52 Append queries to get it all in there.

If I'm setting date to equal the date at the top of that particular column, then appending the hours to the "Hours" field, doesn't that have to be done in 52 seperate append queries? How else would I get new records or get it to start over adding records with each new date?

 

[kvar]

The table I'm attempting to get all that data into has the following fields:
CName, Dept, Div, PosNum, WeekEnding(Date), NumHours

 

[spikepl]

You do need an append query for each column of your budget.

However, if you can manage to get the spreadsheet in as a staging table or link to it as such, with the date as column name, then it should be easy to make some VBA that runs through all columns and runs a query for each.

Update: If this is a one-off (budgets tend to be) then simply making the 52 append queries might be simpler. I'd not call them all from one subroutine though - I think Access could choke on that.

 

[HiTechCoach]

I am pulling it into a temp table first. Then I run an append query that appends the fields I need, but I can only include one of the "hours" fields because I need the "Date" field to equal that date for only those records.
Then the next append query can pull everything but with the 2nd of the "hours" fields, and update the "Date" field to that weeks date. And so on and so on until you're running 52 Append queries to get it all in there.

If I'm setting date to equal the date at the top of that particular column, then appending the hours to the "Hours" field, doesn't that have to be done in 52 seperate append queries? How else would I get new records or get it to start over adding records with each new date?

That is correct. You will need to run an append query for each column to turn it into a row in a table. append queries once. If you use a naming convention where the all have hte same name with a suffix of 01, 02, 03 .... 5. You can use a few lines VBA code to run a loop that executes all the queries


Example:

Public Sub RunAppendQueries()

Dim I As Long

  With CurrentDb
    For I = 1 To 52
    
        .Execvute "qryAppendFromSpreadsheet" & Format(I, "00"), dbFailOnError
    
    
    Next I
  End With

End Sub

 

[kvar]

I think I may have gotten an idea to save me a little time writing code/queries.

Set a variable for the date and a variable for the first field number that contains hours. So initially they would be 10/4/15 and F104.
Then use a loop.
I would only have to write the SQL for the append query once, just calling the variable. Then write the SQL (once) to update the date field WHERE Is Null.
Increment the field name (number since it's a temp table) and increment the date variable by 7.....then run the loop again. Basically telling it to stop when it runs out of fields in the table as the hours fields are the last ones.

I'm thinking this could work I'm just stumped on two things:
1.How to increment a variable by 7 days....but that I can probably Google to figure out.
2. How do you increment a number with a letter in front of it?? Or could I call it "F" &VarName? Or something like that?

I don't recall ever doing anything close to this, but the theory seems sound.
Spikepl maybe something like this is what you were referring to?
And HiTechCoach, thank you. Your idea led me to this one, anything to avoid actually saving 52 queries though!

 

[spikepl]

Perhaps. But honestly, if your programming experience is limited then do not waste your time. Making sure the code works will in such case take longer than simply preparing and running 52 queries.

 

[kvar]

Dim WeekEnd As Date
Dim FieldNum As Integer
Dim selField As String
Dim strInsert As String
Dim strUpdate As String
Set db = CurrentDb

FieldNum = "104"
WeekEnd = #10/4/2015#


    With CurrentDb
        For FieldNum = 104 To 130
            selField = "F" & FieldNum & ""
            strInsert = "INSERT INTO BudgetedOctober_tbl (Department,  PosNum, CName, Exemption, Rate, OTRate, Hours )SELECT [P33 2QFCTemp2].F2, [P33 2QFCTemp2].F1, [P33 2QFCTemp2].F3, [P33 2QFCTemp2].F4, [P33 2QFCTemp2].F5, [P33 2QFCTemp2].F6, [P33 2QFCTemp2]." & selField & " FROM [P33 2QFCTemp2];"
            strUpdate = "UPDATE BudgetedOctober_tbl SET BudgetedOctober_tbl.Week = # " & WeekEnd & " # WHERE (((BudgetedOctober_tbl.Week) Is Null));"
            DoCmd.RunSQL strInsert
            DoCmd.RunSQL strUpdate
            
            WeekEnd = DateAdd("d", 7, WeekEnd)
            Next FieldNum
    End With

It was actually a lot easier than I thought it would be. Just had to concatenate to add the "F" to the field name.
Works beautifully! And soooo much faster than creating 52 queries or field names. I'm sure there is probably a more elegant way, but it's actually really quick so I'll take it.
Thanks for the ideas