If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Stacking Queries"
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" |
#2
|
|||
|
|||
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 "T1 Red Alarm" wrote in message ... 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" |
#3
|
|||
|
|||
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 On Tue, 5 Jul 2005 16:59:02 -0700, T1 Red Alarm wrote: 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" ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#4
|
|||
|
|||
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? "tina" wrote: 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 "T1 Red Alarm" wrote in message ... 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" |
#5
|
|||
|
|||
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 "T1 Red Alarm" wrote in message ... 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? "tina" wrote: 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 "T1 Red Alarm" wrote in message ... 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" |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combining Queries | FrankM | Running & Setting Up Queries | 2 | June 16th, 2005 01:21 PM |
Access2K append queries fail under XP Pro | [email protected] | Running & Setting Up Queries | 1 | May 25th, 2005 08:46 PM |
Q: "Linked Queries" | MarkD | Running & Setting Up Queries | 4 | January 18th, 2005 08:12 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
Exporting Multiple Queries to 1 Excel Workbook with VBA | Anthony | Running & Setting Up Queries | 2 | June 3rd, 2004 07:59 PM |