This may seem strange but i've got a two tables with similar Data one is for History Dates the other is for Future dates. I am trying to get the report to Show the totals or each. But in my report i keep getting both the History and Currents multiple times. And i can't for the life of me fugure out how to stop it. The Querys work fine when they are seperate but combined they just won't function right.

Any help would be appreaciated.

What you are getting is "Non Distinct Row's".

I have trouble with them form time to time.

Let me ask you this, is the data that you are pulling into the query form the same table?
If it is that will be where your problem is, if you are calling data from the same table or with the same criteria you are going to get problems.

If they are not from the same table try going into the query is SQL view and type DISTINCT just after the SELECT key word. That may fix it depending on how you have you tables and query set up.

To get around this perhaps you could try setting up two queries one for one date and another for the other.

Then bring the information onto a report through a SubReport.

With little information here as to the structure of your database I hope this helps. :)

Sounds like your query "joins" the two tables when it should "union" them.

The Tables are

Employee Info - Employee #, Last name, First Name, Department, Date of Hire. Terminated (Y\N)
Time Off History - Fields Employee #(one to many with Employee info), Type Of Time (Vac, Leave of Absence app, LOA Denied. and LOW)(List) and Date of Time off)
Time off Current the Same as History

I'm not using a Key word because the powers that be want all the Info displayed

What it the Difference bettween Joining and Unioning?

I'd include the database but I can't get it small Enuff.

Thanks for the help so far i'll try Subreports and see if that helps

Why don't you post your SQL.

Here's a stupid Question, How does one post their SQL

:confused:

Open the query in design view, switch to SQL on the toolbar, copy and paste here

The SQL of the Query

SELECT [Employee List].Department, [Employee List].[Last Name], [Employee List].[First Name], [Time Off History].[Time Off History], Count([Time Off History].[Time Off History]) AS [CountOfTime Off History], [Time Off History].[Date Booked], [Time off Booked].[Time Off Booked], Count([Time off Booked].[Time Off Booked]) AS [CountOfTime Off Booked], [Time off Booked].[Date Booked]
FROM ([Employee List] INNER JOIN [Time Off History] ON [Employee List].[Employee Number] = [Time Off History].[Employee Number]) INNER JOIN [Time off Booked] ON [Employee List].[Employee Number] = [Time off Booked].[Employee Number]
GROUP BY [Employee List].Department, [Employee List].[Last Name], [Employee List].[First Name], [Time Off History].[Time Off History], [Time Off History].[Date Booked], [Time off Booked].[Time Off Booked], [Time off Booked].[Date Booked];

Your problem is that you have included more than one 1-many relationship in the query. [Time Off History] and [Time off Booked] may both be related to [Employee List] but they are not related to each other. Joining these three tables produces a recordset that appears to duplicate data since each row of [Time Off History] is joined to each row of [Time off Booked]. Therefore, if the first table has 4 rows and the second table has 3 rows, the results set will have 12 rows, 4 rows x 3 rows.


To produce a report that shows all this data, you need to break the reports down into a main report for [Employee List] and two subreports. One for each of the other sets of data.

So the Layout would be

Main Report - Employee Info(from Table)
Sub Report1 - Time off history
Sub report 2 - Time off current

Should i use the Tables for the subreports and then do the Counts and other calulations on the subreports or use Queries to form the subreports? It seems to me that it should work both ways.

But thank you for the help so far now i have to play with formating

Fun wow........

Ok the repeating data problem is no more.

But I have a new Problem.

in my Sub-report I have the following

Field 1 Time off History
Field 2 Count of Time off History
Field 3 Date Booked

So far so good the problem is Time off History is a list box and i need to total each item in the list.

Would I use grouping and sorting for this or is there another way.

The help so far has been wounderful and I thank the community as a whole.

If you want to see the detail, use sorting and grouping and add a count field to the subreport's footer. If you only want summary information, change the query to a totals query.

Should the Counter go in the Page Footer or the Report Footer

and Ialready have a Count in the report shouldn't I be using a Sum of the count? :confused:

The Sub report has 3 fields:

Time off Booked (list box(LOA, TOA, TOD, LOW))
Count of Time off Booked
Date of time off booked

I have to Total the count for each item in the list box.

so if there are 2 LOA's I need a Subtotal for that.

EDIT(after playing with it)

It is a count i forgot to put in a group footer. Now it works.

Thanks much I'm almost done this database now.

The report footer of the subreport or main report. You can use aggregate functions in a page footer. If you need page summaries, you need to calculate them manually.

I've got my Report and the Sub reports

The main report Collums are

Department, Last Name, First Name Vacation Elegibility

The Sub Reports are

Record of time off History and Record of Time off Booked

I need to add 2 colums to the Main one that will add the Total Vacation Days in History and Booked. the 2nd is easy once the first is done it's for the Difference bettween the two. I need to show how many Vacation days are left because Leaves of Absence cannot be taken while any vacation time is remaining.

Can someone help me with this i've got the Querys set up but when i try to add them to the report i get Conflict errors.