Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Code:
     (940010, 003114, 111321, 80, 'Yes', 80, 'Yes'),
          (940011, 003115, 111322, 400, 'No', 0, 'No'),
          (940012, 003116, 111322, 700, 'No', 0, 'No'),
          (940013, 003117, 111322, 70, 'Yes', 70, 'Yes'),
          (940014, 003118, 111323, 890, 'Yes', 90, 'No'),
          (940015, 003119, 111323, 700, 'No', 0, 'No'),
          (940016, 003120, 111323, 690, 'Yes', 90, 'No'),
          (940017, 003121, 111323, 0, 'No', 0, 'Yes'),
          (940018, 003122, 111324, 500, 'No', 0, 'No'),
          (940019, 003123, 111324, 70, 'Yes', 70, 'Yes'),
          (940020, 003124, 111325, 790, 'Yes', 90, 'No'),
          (940021, 003125, 111325, 700, 'No', 0, 'No'),
          (940022, 003126, 111325, 80, 'Yes', 80, 'Yes'),
          (940023, 003127, 111326, 0, 'No', 0, 'Yes'),
          (940024, 003128, 111327, 80, 'Yes', 80, 'Yes'),
          (940025, 003129, 111327, 750, 'No', 0, 'No'),
          (940026, 003130, 111328, 90, 'Yes', 90, 'Yes'),
          (940027, 003131, 111328, 0, 'No', 0, 'Yes'),
          (940028, 003132, 111329, 750, 'No', 0, 'No'),
          (940029, 003133, 111329, 0, 'No', 0, 'Yes'),
          (940030, 003134, 111330, 0, 'No', 0, 'Yes'),
          (940031, 003135, 111330, 860, 'Yes', 90, 'No'),
          (940032, 003136, 111331, 570, 'Yes', 70, 'No'),
          (940033, 003137, 111331, 0, 'No', 0, 'Yes'),
          (940034, 003138, 111331, 525, 'Yes', 75, 'No');
    Above are the values entered for the booking table. In order from left to right they are, bookingcode, delegatecode, eventcode, bookingfee, bedandbreakfast, bedandbreakfastfee and presenting.

    Code:
    (410251, '103 Tonbridge Road, Wateringbury, Nr. Maidstone, Kent', 'Premier Inn', 70, 08715278706),
          (410252, 'Western Avenue, Chatham, Kent', 'Ramada Encore', 80, 08448010313),
          (410253, 'Sandway, Lenham, Maidstone, Kent', 'Chilston Park', 80, 08450727426),
          (410254, '12 New Dover Road, Canterbury, Kent', 'Ersham Lodge', 75, 01227463174),
          (410255, 'London Road, Bapchild, Sittingbourne, Kent', 'Hempstead House', 90, 01322615136);
    This is the venue values... venuecode, venueaddress, venuename, venuecost and venuephone.



    Code:
    (111321, 410251, 205101, 'Project Proposals', '2010-09-07', 'Anthony Keene'),
          (111322, 410251, 205101, 'Database Design', '2010-09-08', 'Alan Barnes'),
          (111323, 410252, 205102, 'Linux vs Windows', '2010-09-14', 'John Grey'),
          (111324, 410252, 205103, 'Software Development', '2010-09-21', 'Ken Wills'),
          (111325, 410253, 205103, 'Business Study', '2010-09-22', 'Carol Ellis'),
          (111326, 410253, 205104, 'Keyboard Events', '2010-09-28', 'Roy Ives'),
          (111327, 410254, 205105, 'Network Topology', '2010-10-04', 'Terry Lyons'),
          (111328, 410254, 205105, 'Network Planning', '2010-10-05', 'Martin Hardman'),
          (111329, 410255, 205106, 'Web Servers', '2010-10-12', 'John Lord'),
          (111330, 410255, 205106, 'Database Servers', '2010-10-14', 'Colin Nauman');
    Finally the event table values... eventcode, venuecode, coursecode, eventname, eventdate, presenter.

  2. #17
    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
    Are you trying to add these records to your tables? or have you already got data in the tables?
    If there is no data in your tables, then the query won't work.
    There is no simple loader for Access.

    The easiest is to create a file such as Event.asc
    with this data for Event
    Code:
    'eventcode','venuecode','coursecode','eventname','eventdate','presenter'
    111321, 410251, 205101, 'Project Proposals', '2010-09-07', 'Anthony Keene'
    111322, 410251, 205101, 'Database Design', '2010-09-08', 'Alan Barnes'
    111323, 410252, 205102, 'Linux vs Windows', '2010-09-14', 'John Grey'
    111324, 410252, 205103, 'Software Development', '2010-09-21', 'Ken Wills'
    111325, 410253, 205103, 'Business Study', '2010-09-22', 'Carol Ellis'
    111326, 410253, 205104, 'Keyboard Events', '2010-09-28', 'Roy Ives'
    111327, 410254, 205105, 'Network Topology', '2010-10-04', 'Terry Lyons'
    111328, 410254, 205105, 'Network Planning', '2010-10-05', 'Martin Hardman'
    111329, 410255, 205106, 'Web Servers', '2010-10-12', 'John Lord',
    111330, 410255, 205106, 'Database Servers', '2010-10-14', 'Colin Nauman'
    Then do a File - Get External Data.... you'll have to read up on that in Help


    Just reread your first post -- you have data in the tables --right?
    Here's a link with some info
    http://en.allexperts.com/q/Using-MS-...Text-Files.htm
    Last edited by orange; 12-27-2010 at 01:21 PM. Reason: clarification

  3. #18
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Yes. That set of data has been entered into access. I have got three other queries working but this one just does not seem to want to work.

  4. #19
    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
    Quote Originally Posted by MarkGLyons View Post
    Yes. That set of data has been entered into access. I have got three other queries working but this one just does not seem to want to work.
    Can you go to the Tables design window and see how the fields are defined?
    I'll create your tables and data at my end and see if I can get the query to work.

  5. #20
    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, I went back to the query
    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;
    and can see issues
    These fields are not meant to be compared
    Code:
    venue.venuename = booking.bedandbreakfast
    I have reviewed your query and created a version
    Code:
    SELECT Venue.venuename, Count(Booking.bedandbreakfast) AS CountOfbedandbreakfast
    FROM (Booking INNER JOIN Event ON Booking.eventcode = Event.eventcode)
     INNER JOIN Venue ON Event.venuecode = Venue.venuecode
    GROUP BY Venue.venuename, Booking.bedandbreakfast
    HAVING (((Booking.bedandbreakfast)="Yes"));
    That gives a result (attached).

    ALSO NOTE: Because you're using
    Booking.bedandbreakfast in an aggregate function you must use HAVING

  6. #21
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    Thank you. I think the version you have created will do the trick. The problem I have still is that in datasheet view the results I should have like you have above are not showing. I am guessing it has something to do with my data types so I will review them and see what I can do.

  7. #22
    MarkGLyons is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2010
    Posts
    11
    It is now working! Thanks for your help, it is very much appreciated.

Page 2 of 2 FirstFirst 12
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