"Stacking Queries"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let's suppost that I have a table with the fields "Employee", "Date of 1st
appt", and "Date of 2nd appt". I have a query that returns "Employee", and
"Date of 1st appt", and I have another query that return "Employee", and
"Date of 2nd appt". So now I have two querys, that have the info I need.
How can I "stack" them so I end up with one file that has "Employee" and "Any
Appt Date"
 
you made a common mistake in table design - putting data in field names (1st
appt, 2nd appt). standard table normalization rules call for two tables:

tblEmployees
EmployeeID (primary key)
FirstName
LastName
(other data the describes an employee)

tblEmployeeAppointments
AppointmentID (primary key)
EmployeeID (foreign key from tblEmployees)
ApptDate
(other fields that describe an employee's appointment)
note: if every appointment an employee has is entered, there is no need to
identify the records as appt1, appt2, etc. obviously the oldest date for an
employee is appt 1, the next-oldest date is appt 2, etc. however, if not all
appointments are entered in the table - such as appt 1, appt 2, appt 4, appt
7 - then you may need a field for the appointment number.

you can link the two tables on their common EmployeeID fields, in a query,
to see all the appointment dates for any employee.

hth
 
A UNION query will do what you want. Note that Union queries must be
created manually -- they are not supported by the query grid. e.g

SELECT * from Query1 UNION ALL SELECT * from Query2

However, the "correct" answer is to properly normalize your database
by creating two tables:

tblEmployee
- PK EmployeeID autonumber
- other fields for employee information

tblAppointments
- EmployeeIDfk LongInteger
- AppointmentDate Date

Using this structure, you can have unlimited appointments by
employees, and you can query the tblEmployee directly to view all the
appointments.

HTH


Let's suppost that I have a table with the fields "Employee", "Date of 1st
appt", and "Date of 2nd appt". I have a query that returns "Employee", and
"Date of 1st appt", and I have another query that return "Employee", and
"Date of 2nd appt". So now I have two querys, that have the info I need.
How can I "stack" them so I end up with one file that has "Employee" and "Any
Appt Date"
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Well, here is my issue;

I have to report on actaul "1st" apps and "2nd" appts (as if they are the
1st or second). And I now have to lump by week. So I have to query both
dates and lump by week. But I also have to give the total number of either
by week. And my biggest problem is that the total of any kind is a new
requirement; I have lots and lots of info already sorted into the tables as
described. Do you see any work around?
 
And my biggest problem is that the total of any kind is a new
requirement

as Jack suggested, you could use a Union query. but the nature of database
development is that there is *always* a "new requirement" being tossed in
your lap. if you don't take the time now the rebuild your tables correctly,
you're just going to keep running into problems that you have to try to come
up with "work arounds" for. it wouldn't be that hard to build the the new
tblEmployeeAppointments and use Append queries to copy the appointment data
from tblEmployees into the new table; then you could just delete the
appointment fields (and their data) from tblEmployees. you'd only have to do
it once, and then from that point on, enter all new data into the
appropriate tables. it's your decision; if you go this route, i do recommend
that you back up the db before doing the data transfer.

hth
 
Back
Top