Need help with a couple queries 1

[debbieg]

This database I'm working on is just kicking my butt! I'm not very good with complex queries. I beg for some help.

Here are my tables:

tblEmployee
*EmpNo
LastName
FirstName
MI
DateHired

tblDeptPay
*EmpNo
*DateEffective
HomeDept
ShiftCode
HourlyPayRate

tblHoursWorked
*EmpNo
*WorkDate
HomeDept
RegHrs
OTHrs
DTHrs
Vacation
Sick
Holiday
Maint
Meet
Funeral
Jury
RawMaterial
WorkComp
Inventory
Other

tblOtherDeptWorked
*EmpNo
*WorkDate
*TempDept
TempDeptRegHrs
TempDeptOTHrs

I need a way to have a report that shows the EmpNo, Employee Name, and Gross Pay for a 2-week pay period. OTHrs and TempDeptOTHrs will be 1.5 times HourlyPayRate, DTHrs will be 2 times HourlyPayRate, and the rest the hours fields will be times HourlyPayRate. I've played around with a crosstab query but have no idea what I'm doing.

I will need another report that shows the EmpNo, Employee Name, Overtime Hours, Overtime Dollars grouped by Department for a 2-week pay period.

There is a one-to-many relationship between tblEmployee and tblDeptPay so I will need to grab the most recent record for each employee.

I appreciate anyone pointing me in the right direction.

Thanks,
Debbie

 

[GingerR]

First a coupla questions:

1) I'm wondering if it's possible that you change your table structure for the table "tblHoursWorked" to be this instead:

*EmpNo
*WorkDate
CategoryID
Hours

Where 'CategoryID' refers to a 'category' which is "RegHrs" or "OTHrs" or "whatever". Then you'd have an additional table called "ChargingCategories" with field "categoryID" (autonumber), a field "Category" which lists "RegHrs","OTHrs", etc...
In this additional table, you could also have a field "Multiplier" where you put in the mutlipliers:

CategoryID Category Multiplier
1 RegHrs 1
2 OTHrs 1.5


This way, if any other 'categories' are added, it's as simple as adding them to this table.

Would it be possible for you to change your structure to this? Then it will be following the rules of 'normalization' and be much easier for you to do calculations throughout your db.

If for some reason you don't want to do this, then you would write a query with a calculated field something like:

TotalPay: RegHrs + (nz(OTHrs,0) * 1.5) + nz(Vacation,0) + nz(Sick,0) + nz(Blah,0) + ......

As you can see, if you decide to add another field, you'll have to change the calculations all over the database, as well as making sure you're putting the correct multipliers in all the right places. And making sure you're including ALL the fields.... yikes....


2) question: what is table "tblOtherDeptWorked" for?

3) Why do you have HomeDept in the tblHoursWorked if it's already in the table tblDeptPay? Can one person have more than one HomeDept at one time? It seems from the structure of table tblDeptPay, that they can only have one at a time.

So let us know this info and we'll move on after that.

Thanks!

g

 

[debbieg]

GingerR,

Thanks for taking the time to help me.

You're right ... I didn't need HomeDept in tblHoursWorked. Thanks for catching that!

I think I've figured everything out except one report. The report needs to look like this:

Employee Hours Postsing Report

Name       11/22  11/23  11/24  11/25  11/26  11/27  11/28
Joe         8.00   8.00   8.00   8.00   8.00   
Mary        8.00   9.00   8.50   8.00   8.00   4.00
Sam         8.00   8.00   8.00   8.00   8.00

I tried to do a crosstab query which seems to work fine for this particular week except that I need to show every day of the week even if some days have no hours.

Here's what I've tried:

SELECT [LastName] & ", " & [Suffix]+", " & [FirstName] & " "+[MiddleName] AS FullName, tblEmployee.EmpNo, tblHistory.WorkDate, nz([RegHrs])+nz([OTHrs])+nz([DTHrs])+nz([Vacation])+nz([Sick])+nz([Holiday])+nz([Maint])+nz([Meet])+nz([Funeral])+nz([Jury])+nz([RawMaterial])+nz([WorkComp])+nz([Inventory])+nz([Other])+nz([TempDeptRegHrs])+nz([TempDeptOTHrs]) AS DailyTotal
FROM tblEmployee INNER JOIN tblHistory ON tblEmployee.ADPFileNo = tblHistory.ADPFileNo
WHERE (((tblHistory.WorkDate) Between [Forms]![frmHoursPostingReport]![txtBeginDate] And [Forms]![frmHoursPostingReport]![txtEndDate]))
ORDER BY tblHistory.WorkDate DESC;

The above query feeds the crosstab query:

TRANSFORM Sum(qryFirstWeekOfPayPeriod.DailyTotal) AS SumOfDailyTotal
SELECT qryFirstWeekOfPayPeriod.EmpNo, qryFirstWeekOfPayPeriod.FullName, Sum(qryFirstWeekOfPayPeriod.DailyTotal) AS [Total Of DailyTotal]
FROM qryFirstWeekOfPayPeriod
GROUP BY qryFirstWeekOfPayPeriod.EmpNo, qryFirstWeekOfPayPeriod.FullName
PIVOT Format([WorkDate],"mm/dd");

The queries work fine except I'm not getting the dates for the columns where no one had any hours. And this will vary from week to week.

And, how do I get the report to pull the right stuff from the crosstab query since the fields are named by the dates?

Thanks,
Debbie

 

[GingerR]

Hi there.

This may seem a bit daunting, but take it one step at a time and it will work out well for you.

Ok, putting crosstab queries into reports is an issue for all of us. I'm assuming you have a button that you click to launch the report. Here's how we're going to do it: You'll make a new table, and each time you click your button, it will fill up with the dates in between your Start and End Dates. It will also number them sequentially. It is these sequential numbers that you will use in your report later instead of dates as labels. Don't worry...they will appear as dates on the report, but be flexible to accomodate any date range. By making this table each time, you will also be able to get ALL the dates you need, even if there are zero hours.

So do this: Create a new table.
Table Name: "ReportDateLabels"
Field: "CrosstabDate", date/time
Field: "ReportDateNumber", number

I'm assuming you have a button that launches the report. In this button's OnClick code, add this code at the beginning:

    Dim i As Integer 'loop control
    Dim datDate, datEndDate As Date
    
    datEndDate = CDate(Me.txtEndDate)
    
    'Delete any records currently in the table ReportDateLabels
    CurrentDb.Execute "Delete * from ReportDateLabels"
    
    'Fill the table up with current date range
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * from ReportDateLabels")
    MsgBox rs.RecordCount
    i = 1
    datDate = Me.txtBeginDate
    Do Until datDate = datEndDate + 1
        rs.AddNew
        rs!CrosstabDate = datDate
        rs!ReportDateNumber = i
        rs.Update
        i = i + 1
        datDate = DateAdd("d", 1, datDate)
    Loop
    Set rs = Nothing

That will fill up the table with the dates we need, and number them sequentially.

Now, in your crosstab query, bring in the new table. Make a join from the new table's CrosstabDate to the existing query's WorkDate field. Make it a left join (arrow pointing from CrosstabDate to WorkDate). In the query grid, make the COLUMN HEADING not be WorkDate any more; instead, make it ReportDateNumber from the new table.

Now when you run the crosstab query, instead of dates it will be numbers sequentially across the top.

In the report, use these numbered columns in the detail section of the report.

As for heading on the report, you can put text boxes instead of labels, and put =dlookup("CrosstabDate","ReportDateLabels","ReportDateNumber = 1")

and so forth for each of the numbered labels.

ok? Try it out and let us know how it goes.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[debbieg]

GingerR,

Wow! Thanks for great instructions. This worked like a charm but ...

The numbers across the top of the crosstab query are 01/01, 01/02 ... 01/06, 12/31. I don't understand that.

And I have a blank record at the top with zeros in column(s) that have no data.

In the report, the headings on the report are in the right order but the not the data - and there is data in columns there shouldn't be.

Everything else worked like a charm! How do you come up with this stuff?????

Thanks,
Debbie

 

[GingerR]

Hi. So it generated the table ReportDateLabels ok?

1) numbers across the top of the crosstab query: you have to change the crosstab query. the field in the crosstab query that is the COLUMN HEADING should be the field ReportDateNumber, which is just an integer. It should look like:

Name         1       2      3      4     5      6     
Joe         8.00   8.00   8.00   8.00   8.00   
Mary        8.00   9.00   8.50   8.00   8.00   4.00
Sam         8.00   8.00   8.00   8.00   8.00

2) blank record: umm...something has to be a "placeholder" for the days that do not have anyone working. So do this: in the crosstab query, under the EmpNo field, put in the criteria Is Not Null. Run the query. Only columns with data should appear. That's ok, don't freak.

Then in the properties of the query, in the ColumnHeadings property, put 1,2,3,4,5,6,7
Doing this will show 7 columns regardless of if there is data or not for each day.

You can use this crosstab query directly in the report by pasting the sql statement into the Recordsource of the report. If you do not put the 1,2,3,4,5,6,7 in the column headings property of the query, the report should balk at you that it "cannot use crosstab of a non-fixed column as a subquery". that's why you have to put in the column headings property...that 'fixes' the columns. This error is what you first questioned: how can i make a report when the columns change all the time? That's the problem Access faces too, and what we just overcame.

Now move on to the report, and set it up as stated above. Then let me know if it's still messed up.

3) i came up with it after months of struggle and settling for less-than-what-I-wanted, then finally figured it out after reading a lot and talking to friends and fiddling around for a long time

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[debbieg]

GingerR,

You're absolutely wonderful!

I had figured out to remove the formatting on the Column Heading. That was a duh moment!

I had tried to put "is not null" in EmpNo but then I didn't get all the columns. Your suggestion to use fixed columns, did the trick. I don't use crosstab queries a lot and forgot about them.

I appreciate your sharing so I didn't have to struggle for months ... since I don't have months to struggle!

Thanks so much for sharing!
Debbie

 

[GingerR]

Glad it worked out for you!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244