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

    Query question (A2k)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    The file attachment in <post#=284692>post 284692</post#> includes qryConfirmation. Some changes have been made to the database since that query was created, and I am having difficulty getting it to behave correctly.

    The query is needed to develop a record source for confirmation/receipts to be emailed to participants in an upcoming conference. When I first created the query, I did not have any other queries in the database. Now I'm confused because I have tables for Guests and Billing and queries for Guests and Billing, and don't know if I should include the tables or the queries in my query.

    I think this also may have something to do with inner and outer joins, but I'm not familiar enough with that concept to figure it out.

    Thanks for any help!

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

    Re: Query question (A2k)

    The query qryConfirmation as it is in the database you posted earlier needs to be adapted a little bit: the field curStudentPrice has been moved from Billing to Participants, so the query now contains an invalid reference to Billing.curStudentPrice. That should be changed to curStudentPrice from Participants. Other than that, it should be OK, as far as I can see.

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

    Re: Query question (A2k)

    Got it! And thanks for the Help 19, I've always wondered how you do that.

    I've almost got this query returning the results I need. I do want to display the information from sbfBilling as it is on that sbf. I can get the curStudentPrice and curGuestTotal to display, but the results that appear in txtTotalOutput are missing in action. I think I need to add a calculated field. Is that correct? If so, I'll try to follow the help menu and figure it out without bugging you anymore today! <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

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

    Re: Query question (A2k)

    Yes, you will need a calculated field, but before you do that, test the query thoroughly to see if it returns the correct values.

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

    Re: Query question (A2k)

    Hans,

    It is returning the correct values as it is, it's just missing the "total". It is however returning multiple records for each participant due to multiple classes and students per participant. I believe we covered this before, but its been a while. You pointed me to a Microsoft Knowledge Base article to fix it in a word doc (which is what I will use as the basis for my confirmation). I don't recall immediately if there was another fix available.
    Was it the inner/outer join thing?

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

    Re: Query question (A2k)

    In this case, it doesn't have to do with inner or outer joins. You're trying to do too much in one query.

    If you want to get the data into a Word document, you're going to have to use Automation. This is not easy, it requires that you are comfortable with both Access VBA and Word VBA, but it is very flexible. Moderator <!profile=WendellB>WendellB<!/profile> has a tutorial on Automation on his website (there is a link in his profile, look for Support > Tutorials.)

    You could also create a report based on the Participants table, with subreports for classes, guests and financial data, all linked to the main report by intParticipantID.

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

    Re: Query question (A2k)

    Unfortunately I don't have enough time left to start working on Word VBA. So shall I abandon the query notion for now and work on reports and subreports? To clarify again, what I need is a document (or email body) to be emailed to my participants listing all the pertinent information as a receipt/notice of confirmation. After all info has been entered I will use a command button to automatically email it. Does that sound to complicated for me to accomplish in the next few weeks?? If producing the reports are similar to forms, I think I can get through it. I need your straightforward advice, you are familiar with my (in)experience level by now.

    Thanks!

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

    Re: Query question (A2k)

    Designing a report is like and yet unlike designing a form. The tools are the same, but reports have grouping and summarizing options that forms lack. You could start by taking a look at the Northwind sample database that comes with Access (you'll find it in C:Program FilesMicrosoft OfficeOfficeSamples in a default installation). It contains several examples of reports and subreports with calculations.

    Sending off individual reports by e-mail requires extra coding, but Loungers can help you with that.

    All in all, it won't be easy, and you'll have to work hard. If time is critical, you might consider getting an experienced developer to build the database. It shouldn't be hard to find them in California.

  9. #9
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (A2k)

    Attached is some code that will generate individual emails with the information from the fields listed. It grabs a record based on ClientID, loops through a subset of listings(each having a ListingID) attached to the ClientID and then through a second subset of Listings linked on ListingID. Once it gathers up all of the info for that person it creates an Outlook message and sends it. You will in all likelihood get the annoying messages from Outlook's security popping up, so it is helpful to go to the web, search on "Express ClickYes" and download the freeware utility to do the clicking for you.

    You need a reference to the Outlook Object Library in VBA. The alternative is the Redemption Library which would be more elegant but more complicated to use.

    If you can parse the code, you should be able to adapt it to your needs quite easily. At least this gives you an idea what you are up against. (BTW, the naming conventions are less than perfect). I needed this to work, not be pretty and it was for my own use.

    Hope this helps.

    Peter N
    Attached Files Attached Files

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

    Re: Query question (A2k)

    Thank you so much Peter. I'm gonna take a look at it today-Honestly, I'm a coding newbie but I'll do my best. I've only got about 3 weeks to get this all done, so wish me luck!

  11. #11
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (A2k)

    If it is any comfort, I am still relatively new to writing my own code. This is mainly adapted from one of my reference books and then tweaked with some help from the invaluable Hans. I don't think I will ever get the hang of writing my own SQL statements. As I said in the previous post, if you can figure out what the code is doing at any given point, it is pretty easy to adapt it to a new use.

    Good Luck.
    Peter

Posting Permissions

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