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
|
|||
|
|||
joining multiple queries
Joining multiple queries - the database records interest
in courses and classes held and I have created several different queries: One to count students in a course based on yes for form completed (from the enquiry form/table) One to sum the fees paid once again from the enquiry form/table One to show the program hours for the course - taken different table (course information sub form) I need to specify a date range - start date, end date I need to show the results in a report and have it multiply number of students by number of course hours - I've done this bit in the report, not a query. The course date is taken from the enquiry form/table The course names are taken from course information table I have done all of this but I get the results shown several times in the report eg the results for the course on 30/8/04 are correct but appear twice CourseName SumOfPaidAmt CountOfForm Completed Hours Course Date Internet and Email $1,934.00 18 8 18/3/04 Internet and Email $1,934.00 18 9 18/3/04 Internet and Email $1,414.00 20 8 30/8/04 Internet and Email $1,414.00 20 9 30/8/04 Intro to Computers $1,000.00 13 20 12/2/04 Leadlighting $120.00 4 20 28/6/04 Overlocking $336.00 8 20 9/6/04 Can something be written within the query to stop this or is it to do with fields used for joining - it is possible that two different courses could run on the same date. I guess it has something to do with my joins - I've linked 2 queries using the course date and one of these queries to another using course ID thanks auntie jack |
#2
|
|||
|
|||
hi,
I think is is your joins. something you need more than 1 joins. look the 2 querys over and find and link all the common fields. I have had querys that had to have up to 4 joins to some repeating data as i call it. -----Original Message----- Joining multiple queries - the database records interest in courses and classes held and I have created several different queries: One to count students in a course based on yes for form completed (from the enquiry form/table) One to sum the fees paid once again from the enquiry form/table One to show the program hours for the course - taken different table (course information sub form) I need to specify a date range - start date, end date I need to show the results in a report and have it multiply number of students by number of course hours - I've done this bit in the report, not a query. The course date is taken from the enquiry form/table The course names are taken from course information table I have done all of this but I get the results shown several times in the report eg the results for the course on 30/8/04 are correct but appear twice CourseName SumOfPaidAmt CountOfForm Completed Hours Course Date Internet and Email $1,934.00 18 8 18/3/04 Internet and Email $1,934.00 18 9 18/3/04 Internet and Email $1,414.00 20 8 30/8/04 Internet and Email $1,414.00 20 9 30/8/04 Intro to Computers $1,000.00 13 20 12/2/04 Leadlighting $120.00 4 20 28/6/04 Overlocking $336.00 8 20 9/6/04 Can something be written within the query to stop this or is it to do with fields used for joining - it is possible that two different courses could run on the same date. I guess it has something to do with my joins - I've linked 2 queries using the course date and one of these queries to another using course ID thanks auntie jack . |
#3
|
|||
|
|||
do you mean multiple joins in the last one to combine
all, or earlier in the process - I've tried all the combinations I can think of and included other fields that I don't want and tried linking them but still no result - any more suggestions thanks anyway -----Original Message----- hi, I think is is your joins. something you need more than 1 joins. look the 2 querys over and find and link all the common fields. I have had querys that had to have up to 4 joins to some repeating data as i call it. -----Original Message----- Joining multiple queries - the database records interest in courses and classes held and I have created several different queries: One to count students in a course based on yes for form completed (from the enquiry form/table) One to sum the fees paid once again from the enquiry form/table One to show the program hours for the course - taken different table (course information sub form) I need to specify a date range - start date, end date I need to show the results in a report and have it multiply number of students by number of course hours - I've done this bit in the report, not a query. The course date is taken from the enquiry form/table The course names are taken from course information table I have done all of this but I get the results shown several times in the report eg the results for the course on 30/8/04 are correct but appear twice CourseName SumOfPaidAmt CountOfForm Completed Hours Course Date Internet and Email $1,934.00 18 8 18/3/04 Internet and Email $1,934.00 18 9 18/3/04 Internet and Email $1,414.00 20 8 30/8/04 Internet and Email $1,414.00 20 9 30/8/04 Intro to Computers $1,000.00 13 20 12/2/04 Leadlighting $120.00 4 20 28/6/04 Overlocking $336.00 8 20 9/6/04 Can something be written within the query to stop this or is it to do with fields used for joining - it is possible that two different courses could run on the same date. I guess it has something to do with my joins - I've linked 2 queries using the course date and one of these queries to another using course ID thanks auntie jack . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Merging with Access queries - Multiple | CPutnam | Mailmerge | 5 | August 21st, 2004 01:11 PM |
Multiple Queries | Richard | General Discussion | 3 | August 16th, 2004 11:26 PM |
joining queries!! how! | manish | Running & Setting Up Queries | 2 | June 18th, 2004 02:53 PM |
Exporting Multiple Queries to 1 Excel Workbook with VBA | Anthony | Running & Setting Up Queries | 2 | June 3rd, 2004 07:59 PM |