Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Queries (A2k)

  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries (A2k)

    I have a question about query design..I have 3 tables that have many to many relationships and 3 junction tables connecting them. When I design my query (which I will use a data source for a mail merge) do I use the junction tables, the main tables or both?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries (A2k)

    If you need fields from two of the main tables in your mail merge, you must also include the join table in the design of the query, even if you don't include any fields from the join table in the query. In some cases, you may be able to get by with one of the main tables plus a join table. It all depends on what you need in the mail merge.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    My query is not returning results, here's what's happening:
    I need one table (the result of the query) to use as a data source for a mail merge. I've included my students table, classes table, and billing table and StudentClasses (join table). I suspect that things aren't linked up correctly, I've gone through as many resources on line as possible, and cannot figure out what I'm doing wrong.

    Please <img src=/S/help.gif border=0 alt=help width=23 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Queries (A2k)

    If you post your SQL statement, we can see if we see any problems. Easiest way is to open the query in design view and then choose the SQL view from the toolbar. Then you can copy and paste that into a reply.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    Wow, had no idea that part even existed.....Here it is--Thanks Wendell

    SELECT Students.intStudentID, StudentClasses.intClassID, Billing.BillingType, Billing.CCtype, Students.chrSalutation, Billing.CCAmt, Billing.CheckNumber, Billing.CheckAmt, Students.chrLastName, Students.chrFirstName, Students.chrMiddleInitial, Students.chrSuffix, Students.chrDegree, Students.chrTitle, Students.chrOrganization, Students.chrAddress, Students.chrCity, Students.chrState, Students.chrZipCode, Students.chrPhoneNumber
    FROM (Students INNER JOIN (Billing INNER JOIN StudentBilling ON Billing.intBillingID = StudentBilling.intBillingID) ON Students.intStudentID = StudentBilling.intStudentID) INNER JOIN StudentClasses ON Students.intStudentID = StudentClasses.intStudentID;

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Queries (A2k)

    It looks to me as though there isn't anything really wrong with your query - though you might want to include your classes table as well so you can get a description of the class as well as it's internal ID number. I suspect you may be missing data in one or more of your linking tables used to set up the many-to-many joins. I haven't closely followed your previous threads on the table design but it seems reasonable, so the data is what makes me suspicious. One way to do that is to delete one main table at a time to see if you suddenly start getting records. You might start with Classes first, then try the Billing table next. If you don't get any records with either of those, then the missing data is in your linking tables, so try deleting StudentClasses first. If that doesn't work, then delete both Billing and StudentBilling. Hopefully that process will identify what's wrong.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    I think you're right, and I will check it out and let you know..

    Thanks a bunch!

  8. #8
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    Okay, I deleted the tblStudentBilling (which upon checking was empty) and ran the query. I got a message stating that 38 records were going to be pasted. I've only got 7 records in my tblStudents. Each of those records has one subrecord for billing info, one subrecord for guest info and 3 subrecords for classes.

    So, #1-My tblStudentBilling isn't set up right
    #2-I'm getting too many records returned

    Also, as I'm going to eventually use this query as a datasource for a mail merge, should I be doing a "make table" query?

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> and <img src=/S/confused.gif border=0 alt=confused width=15 height=20> ....as usual

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Queries (A2k)

    Are you expecting one record per student for the report?
    If so then your query won't give you what you want. It will give you as many records as there are when you multiply the number of records in Students * Billing * StudentBilling * StudentClasses.

  10. #10
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    Yes, I only want one record per. I need to incorporate all of the information into a confirmation email/receipt. It will list all of the student information, the 3 classes they are taking and the method of payment, amount, etc.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries (A2k)

    You can get around this in Access or in Word, but it's not trivial in either.

    You can create a query in Access that concatenates the three classes taken by a student into one string. See the thread starting at <post#=263987>post 263987</post#>.

    You can also use the query with the repeated student info and use IF fields in Word to manipulate the mail merge in Word - see WD2000: How to Work Around Duplicate Names in Mail Merge Data

  12. #12
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Well, that should keep me busy for the rest of the week--I've printed the thread and the knowledge base article and will dive in tomorrow-Time for <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks Hans

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    Hans,

    I've worked through the knowledge base article and followed the example and pretty much have that part figured out....However, in order to go any further, I still need to get my query correct. It returns the proper results if I remove the tblStudentBilling, but that is a junction table between tblStudents and tblBilling. Also, tblStudentBilling isn't returning any records, even though I have 7 records in my tblStudents. I've checked the join properties and the table relationships and all seems to be fine. Can I send it over to you to look at, or do you have any ideas without looking at it??

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries (A2k)

    Hi Gabi,

    One thing you could try (but I can't tell if that is what you need) is to change the join between the tables. Double click the line between the tables in query design view, and select the option to return all records from tblStudents (this is called an outer join).

    If you would like to post your database for me and others to look at, see <post#=261855>post 261855</post#> for instructions.

  15. #15
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (A2k)

    Hans, I can't get it zipped up small enough. I can only get it down to 400kb. That's with deleting all non relevant objects, several records and compact and repairing. I tried the outer join that you suggested and got an error message-"The SQL statement could not be executed because it contains ambiguous outer joins", then it says something about creating a separate query first. If you need more info on the error message, let me know.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •