Displaying/ stacking fields in a MS Access 2000 report

[romeyp]

I am working on a report that keeps track of Vacation time, sick time, and holiday time available to our employees.

I have it all set up but the problem I am having is this information is updated each month. I want to be able to show the balance from the previous month as well as the current month at the same time.

My intent is once I have this working correctly to not recreate the wheel each month when I have to run this report. I just want to be able to import the table and from there I can run a macros to append the new info.

One other problem I am having is I appended 3 months worth of info, and now the output is displayed in duplicate on the report when I run it.

Any help that can be provided would be of great help.

 

[GingerR]

ok let me get this straight: you have the detail info, but how is it unique? is there a month or date associated with each record? i mean do you want to keep each month's worth of data, and just add another month to that? or do you want to delete the previous month's data? is there a date field in your table so you can separate the months?

it would help to have your table structure (fields) listed here.

if you have a date or a month designated (i suggest if monthly to use the first of each month for sorting, subtracting etc, i.e. 1/1/03 represents january 2003, etc).

then i think this is what could happen: it's time to import some new data. on a little maintenance form, you enter the month into a text box (or like i said, put in like 7/1/03 for july 2003 data). hit a button. it runs a bunch of macros that
1) takes the new data and imports with the date of 7/1/03 in the date/month field, then 2) does all your calculations, etc.

then you can hit a button that opens a report which shows you details or calculations or whatever it is you want of 1) the current month (entered on your form - 7/1/03, or whatever you want) and the previous month (calculated so it knows it's 6/1/03 data). am i close?

 

[romeyp]

Your are correct in what I am trying to do.
My problem is that we use a different numbering system for our months ie.- April =1, May =2, June =3 ect...
On my report I want to display the vaction info for each employee on one page each with each month number display with the corresponding information for that month.
My tables and their contents are as follows:

Employee info - employee number, employee name, vacation
balance, holiday balance, sick balance,
vacation rate, holiday balance, sick
balance, level, company, org

Accruals - employee number, vacation accrued, holiday
accrued, sick accrued (all of these fields are
for each month- April, May, June)

Taken - employee number, sick taken, holiday taken,
vacation taken

Month - Accounting Month (our number for the month),Name of
month

When I run a query to combine all of this information due to the employee name and number being in each month I get 3-6 dupes of that record.

That same information is showing up in my report with the dupes in my detail section where I want to display it only once for each month as so:
Accounting Month Vacation Accrued Vacation Taken
1 4.0 3.0
2 4.0 0.0

I know I have presented you with a lot of confuseing info, but any help that you can provide would be greatly appreciated. ( I just started this job which is my dream job, and I am already stuck on my first project)

 

[GingerR]

1) is an employee number only found once in the tables EmployeeInfo and Taken?
2) and if i read you right, each employee is in Accrual once for each month right?
3) also table MONTH has each month listed only once right?

4) what is your query sql (if you dont know what i mean: in the menu bar choose VIEW+SQL, copy and paste that here)

 

[romeyp]

I am sorry that it has taken sometime for me to reply, but my office is moving at the same time.
I have pasted the SQL as you said.

1)The Employee number is found in the EmployeeInfo, Accrued, and the Taken table.
2) Each employee accrues time each month.
3) Yes table Month has each month listed only once.

As I said previously the report works fine but when I combine the EmployeeInfo, Accruel, and Taken with all of the information for the past three month I get 3 duplicates for each record that has information in each month.

The statment below is going to look a bit of a mess because I am doing what works out to be a sum of Vacation,Sick, and Holiday.


SELECT DISTINCT [Employee Info].*, ([Vacation Balance]+([Vacation Accrued]-[Vacation Taken]))/8 AS Expr1, ([Sick Balance]+([Sick Accrued]-[Sick Taken]))/8 AS Expr3, ([Holiday Balance]+([Holiday Accrued]-[Holiday Taken]))/8 AS Expr2, Accruals2.[Vacation Accrued], Accruals2.[Holiday Accrued], Accruals2.[Sick Accrued], NZ([Sick Accrued],0) AS Expr5, Accruals2.[Accounting Month], Accruals2.[Accounting Year], Taken2.[Sick Taken], Taken2.[Holiday Taken], Taken2.[Vacation Taken], Taken2.[Jury Duty Taken], Taken2.[Bereavement Taken], Taken2.[STD Taken], Taken2.[Family Leave Taken], Taken2.[TWOP Taken], NZ([Training Taken],0) AS Expr4
FROM ([Employee Info] LEFT JOIN Accruals2 ON [Employee Info].[Employee No] = Accruals2.[Employee No]) LEFT JOIN Taken2 ON [Employee Info].[Employee No] = Taken2.[Employee No];

 

[GingerR]

what if you make it a TOTALS query?
or in the query properties (VIEW+PROPERTIES) and GROUP BY everything.

or go into the query properties and make Unique Records or Unique Values YES and see what happens.

are you trying to only see the data for one particular month?
looks like the MONTH is not displayed in this query (from accruals table)?
does table TAKEN have taken amounts for each month? or just one record per employee which pertains to the current month?

if you want, you can compact and zip the db and send it to me: rowe147@hotmail.com and i will take a look.