Results 1 to 9 of 9
  1. #1
    khartoum is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Location
    Liverpool
    Posts
    23

    Diplaying all row fields in Cross Table Query

    Hello all I have a cross table query which gives me:
    - Names along the top


    - Times down the side
    - Appointment details in the detail bit as below

    TRANSFORM First(tblappts.HavingDone) AS FirstOfHavingDone
    SELECT tblappts.Time
    FROM tbltimes RIGHT JOIN tblappts ON tbltimes.Times = tblappts.Time
    WHERE (((tblappts.ApptDate)=[Forms]![frmmain]![appt3]))
    GROUP BY tbltimes.Times, tblappts.Time
    PIVOT tblappts.Stylist;

    Is there a way where i can get all the times to show on the row headings even if there is not an appointment associated with it
    any help appreciated thanks

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Have you tried a LEFT JOIN

    FROM tbltimes LEFT JOIN tblappts ON tbltimes.Times = tblappts.Time

    Thanks

  3. #3
    khartoum is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Location
    Liverpool
    Posts
    23
    Yep, tried, still only brings up those where there is details held, am assuming it is to do with it being a cross table qry cheers

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Can you post

    tblappts & tbltimes with a few dummy data ?

    Thanks

  5. #5
    khartoum is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Location
    Liverpool
    Posts
    23
    tbltimes is just times 08:00 thru to 20:00 in fifteen minute intervals i.e. 08:00 08;15 so on

    tbl appts is:
    clientname text
    apptdate date/time short date
    time date/time short time
    stylist text
    havingdone text
    completed yes / no

    sql for query is:
    TRANSFORM First(tblappts.HavingDone) AS FirstOfHavingDone
    SELECT tblappts.Time
    FROM tblappts RIGHT JOIN tbltimes ON tblappts.Time = tbltimes.Times
    WHERE (((tblappts.ApptDate)=Date()))
    GROUP BY tbltimes.Times, tblappts.Time, tblappts.ApptDate
    PIVOT tblappts.Stylist;

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Don't know if I have guessed things at your end properly.

    Just check out if below gives some guidelines :
    The sub-query :

    Code:
    SELECT 
        tblappts.testTimes_fk, 
        tblappts.havingdone, 
        tblappts.stylist
    FROM 
        tblappts
    WHERE 
        (((tblappts.ApptDate)=Date()));

    The final query to run :

    Code:
    TRANSFORM First(qryAppts.havingdone) AS FirstOfhavingdone
    SELECT 
        tblTimes.testTimes
    FROM 
        tblTimes 
        LEFT JOIN 
        qryAppts 
        ON 
        tblTimes.testTimes = qryAppts.testTimes_fk
    GROUP BY 
        tblTimes.testTimes
    PIVOT 
        qryAppts.stylist;
    Thanks

  7. #7
    khartoum is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Location
    Liverpool
    Posts
    23
    i'll try this and let you know thanks

  8. #8
    khartoum is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Location
    Liverpool
    Posts
    23
    Much obliged recyan, sub queries galore but worked great!

  9. #9
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Can't just get rid of the habit of thinking sub-queries first & then thinking queries.
    All the same, glad, you found it helpful.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Two ID fields with cross-populating Look-Up
    By jebowers in forum Access
    Replies: 2
    Last Post: 04-26-2012, 03:04 PM
  2. Replies: 2
    Last Post: 02-02-2012, 12:18 PM
  3. Cross Tab Query.
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 01-04-2012, 01:29 PM
  4. Replies: 11
    Last Post: 09-15-2011, 03:52 PM
  5. cross table problem
    By humanmaycry in forum Queries
    Replies: 3
    Last Post: 07-20-2011, 12:08 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