Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11

    Hello! Help required if possible please...

    Hello, I am new to the forums and generally new to Access as well. I have been creating a database using MySQL and have now imported the data tables into Access. Where I am currently struggling is queries, I have a number of successful queries created but I am wondering how I create a particular query in Access.

    I have 5 tables...

    Booking - bookingcode, delegatecode, eventcode, bookingfee, bedandbreakfast, bedandbreakfastfee, presenting?

    Delegate - delegatecode, delegatename, delegateaddress, delegatephone

    Course - coursecode, coursedate, coursetitle, coursefee, courseduration

    Venue - venuecode, venueaddress, venuename, venuecost, venuephone

    Event - eventcode, venuecode, coursecode, eventname, eventdate, presenter

    The query as created in MySQL is as follows,

    Code:
    select venuename, count(bedandbreakfast) as totalbb
    from venue, booking, event
    where booking.eventcode = event.eventcode
    and event.venuecode = venue.venuecode
    and bedandbreakfast='Yes' group by venuename;
    This basically lists the number of delegates requesting bed and breakfast at each hotel.



    I just don't now know how to create that exact query in Access...?

    Thanks for your time.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't have Acc2007.

    If you can open the Access query window, you will have an option to use SQL as compared to a query wizard.

    If you copy your existing SQL, that you listed, and paste it into the query window as SQL, you should be OK.

    Again, I'm using Acc2003, but similar functionality will be in 2007.

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    You may not be OK. First of all, GROUP BY clauses have to be followed by HAVING clauses, which serve as replacements for WHERE clauses. WHERE clauses are not allowed when mixed with GROUP BY.

    Secondly, you're query statement represents a CROSS JOIN, which yields very specific results, and sometimes they are not even inferrable because the input request becomes so complex due to the sql statement, so make sure you know that what you're getting in the output is exactly what you want!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not so Adam,

    Here is some Access2003 code I just set up for some exisiting tables
    Code:
    SELECT
     CustomerRelationType.RelationTypeName
    , Count(Customer.CustomerName) AS CountOfCustomerName
    FROM 
    CustomerRelationType
    ,CustomerRelatedCompanies 
    ,Customer 
    WHERE 
    CustomerRelatedCompanies.CustomerNo = Customer.CustomerNo AND
    CustomerRelationType.RelationType = CustomerRelatedCompanies.RelationType
    AND CustomerRelatedCompanies.CustomerNo Like 2
    GROUP BY CustomerRelationType.RelationTypeName;
    Works fine.
    jack

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Interesting. Would've never thought. Hope it works for him too!

  6. #6
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Thank you for your help.

    I pasted the exact SQL statement above in my first post into the query wizard and then saved the query and closed it. However once I tried to open the query again I got a "Data type mismatch in criteria expression" error. Not sure if that is a result of what ajetrumpet was talking about, is there anything I can do to correct this, maybe retype the SQL in a different way?

    Thanks again.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Mark,
    The error message you describe indicates that you have some text data and some numeric data. When you have text values, they must be within quotes - usually double quotes ". Numeric values have no quotes.

    For text, sometextfield = "tvalue"
    For numerics , someNumeric = value

  8. #8
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Quote Originally Posted by orange View Post
    Mark,
    The error message you describe indicates that you have some text data and some numeric data. When you have text values, they must be within quotes - usually double quotes ". Numeric values have no quotes.

    For text, sometextfield = "tvalue"
    For numerics , someNumeric = value
    Thanks, that has helped me but would that also apply to the currency data type as well? Does any values in currency type have to be in double quotes?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No, currency is considered numeric.

    Can you post your SQL?
    Can you show the fields in the tables and their data types?

  10. #10
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Ok I have edited the text values so they have double quotes and tried the query shown above. This time the query saves without errors but when I go to datasheet view it only has the field title and no results are shown at all.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post the sql.

  12. #12
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Code:
    SELECT venue.venuename, Count(booking.bedandbreakfast) AS totalbb
    FROM event, venue INNER JOIN booking ON venue.venuename = booking.bedandbreakfast
    WHERE (((booking.eventcode)=[event].[eventcode]) AND ((event.venuecode)=[venue].[venuecode]) AND ((booking.[bedandbreakfast])='Yes'))
    GROUP BY venue.venuename;

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this sql
    Code:
    SELECT venue.venuename
    , Count(booking.bedandbreakfast) AS totalbb
    FROM 
    event, venue, booking 
    WHERE
    venue.venuename = booking.bedandbreakfast AND
    booking.eventcode =[event].[eventcode]  AND 
    event.venuecode=[venue].[venuecode]  AND 
    booking.[bedandbreakfast] = "Yes" 
    GROUP BY venue.venuename;
    You can pretend to create a new query, go to the sql view and paste this in, and try it.

  14. #14
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Quote Originally Posted by orange View Post
    Try this sql
    Code:
    SELECT venue.venuename
    , Count(booking.bedandbreakfast) AS totalbb
    FROM 
    event, venue, booking 
    WHERE
    venue.venuename = booking.bedandbreakfast AND
    booking.eventcode =[event].[eventcode]  AND 
    event.venuecode=[venue].[venuecode]  AND 
    booking.[bedandbreakfast] = "Yes" 
    GROUP BY venue.venuename;
    You can pretend to create a new query, go to the sql view and paste this in, and try it.
    Thanks but I am still not getting any results in datasheet view. Does it have something to do with relationships? The data types for venue name and bedandbreakfast are both text and now have double quotes. Would single quotes make a difference?

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not that I can see.
    Can you send a small version of your database ?
    Or give some sample data?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Required Field Help
    By brandonb in forum Access
    Replies: 4
    Last Post: 11-13-2009, 11:18 AM
  2. immediate help required
    By pdurgi in forum Access
    Replies: 1
    Last Post: 06-05-2009, 08:21 PM
  3. help required badly
    By pradeep_siemens in forum Programming
    Replies: 0
    Last Post: 05-28-2009, 04:41 AM
  4. Object required
    By duckie10 in forum Access
    Replies: 1
    Last Post: 05-15-2009, 02:11 PM
  5. Replies: 0
    Last Post: 02-15-2007, 03:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums