How to Union

[Thecherub]

I know this has probably been asked before but when it comes to hard coding i'm lost.

I've got Three tables

Table Fields
Employee List : Employee #, lastname, firstname, vacation_days
Time off history : Employee #, Timeoffbooked, Date_booked
Time off booked : Employee #, Timeoffhistory, Date_booked

Timeoffbooked is a List: LOA Approved, LOA Denied, LOW, Vacation

Time off History and booked are linked to employee list with one to manys.

I need to get the two vacation munbers to add together so that i can calculate vacation remaining.

I'm lost here.... I may have to redesign but i'm trying to avoid that since this is the second redsign as it is.

Thank you in advance.

 

[dcx693]

Not sure why you have time off history and booked in separate tables (unless you need to partition them due to size or security). I'd just put them into one table...but since you're not keen on redesigning...you can union them together if you need to look at time off in general.

Not sure what you mean by

Timeoffbooked is a List: LOA Approved, LOA Denied, LOW, Vacation

Are you saying the field is somehow a list? What does that mean?

 

[Thecherub]

the list

Timeoffbooked is a List: LOA Approved, LOA Denied, LOW, Vacation

The row source type is Value List
the row source contains: LOA Approved, LOA Denied, LOW, and Vacation.

Not sure why you have time off history and booked in separate tables

This is done as the users want to list what dates have already Happened seperatly from the ones that have not yet happened.

It makes my life difficult but i'm not the end user.

 

[dcx693]

Re: the list

OK, which field(s) in the tables have the info on what dates the employee actually took off? If you want to union the two tables, you'll need to include those in the union.

Look in the Access help for information on Union queries. They're pretty easy to create.

 

[Thecherub]

I looked

at the help files and as is usuanly the case the convyed information without illumination.

The fields that contain the information i need are TimeoffBooked and Timeoffhistory. I need to count these in each and add them together but only for the Critera "Vacation". This is where the help fails to help.

I also need Lastname and Firstname from the Employee List Table to sort the datalist. This query will become my third sub report on a main report and i need those to provide the structure.

confusing Eh?

on a sub note how would you set up the time off data in one table. i guess you would use critera like <=date() and >=date() to detrmine history and current in the queries and reports....

and i could still set up my sub reports but i would not need to add together from seperate tables.....

the lightbulb go's on.........

 

[dcx693]

The help file is not that bad. In fact, it lays out the steps quite nicely:[/quote]Combine data in fields from two or more tables using a union query.

Union queries combine corresponding fields from two or more tables or queries into one field. When you run a union query, it returns the records from corresponding fields in the included tables or queries.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Union.
5. Enter SQL SELECT statements combined with either the UNION operation if you don't want to return duplicate records or the UNION ALL operation if you do want to return duplicate records.
   
   Note Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.
   [/list=1]It then goes on to give an example with criteria.
   
   I suggest you union the two time off tables first. Make sure that produces the results you want. Then create another query with that first query as the source, using "Vacation" as the criteria. Finally, join that to your employee information table in a 3rd query in order to get at the name info.
   
   Then you can worry about getting it into your report.
   
   I know, lots of steps. But I find it's easier to think of a large project as a series of small steps.

 

[Thecherub]

Thank you

I'll try that if putting everything on one table dosen't work. I'm trying that now. it solves all of my problems.

Thank you for sugesting it. it made me think.