Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Crosstab query help, please (2007)

    Hi y'all

    I'm trying to get my head around a Crosstab, and I'm hoping someone can guide me, please.

    I have a Database of Clients that lists all the jobs they've had and the current status of that job.

    Eg
    Joe Bloggs got a Job Placement in Coca Cola on 1-Feb-07, and when he finished there, his Job Status was set to Discontinued
    Joe Bloggs got another Job Placement in Pepsi Cola on 30 April 2007, and his Job Status is 'Current'

    I want to Query the Database to show me only the most recent Job Status. Therefore, in the above example, the query would only return the 2nd Job Placement at Pepsi, because that was the most recent job he was placed in.

    I've tried creating a Crosstab Query using "LAST" as the Total Column, but I get loads of Columns in the result, ie, each Status as a Column Heading

    All I want is the person's name and the last Status to display in the Query
    Eg
    Joe Bloggs | Current
    Paul Smith | Discontinued
    John Jones | In Progress
    Clark Kent | Current

    Etc

    Can someone please advise how I form the Query to get this result?

    Thank you so much in advance for your help.

    Brian

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

    Re: Crosstab query help, please (2007)

    You don't need a crosstab for this. Instead:

    Create a query based on your table.
    Add the fields you want to display to the query grid, as well as the relevant date field.
    If you don't want to display the date, you can clear its Show check box.
    Enter the following expression in the Criteria line for the date field, substituting the correct names:

    (SELECT Max(t.[DateField]) FROM [tblClients] AS t WHERE t.[ClientID] = [tblClients].[ClientID])

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help, please (2007)

    Thanks Hans, although I think I'm doing something wrong (probably because there are 3 Tables--refer Screen Cap).

    I tried modifying the SQL you wrote, but I ended up with 88,000 results, which is virtually every placement, instead of 5,000 (the appox number of Clients).

    Instead of using Joins in this Query, I created a Query with all the Fields I needed and then tried to Query that. Is that easier, or isn't it possible?
    Attached Images Attached Images

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

    Re: Crosstab query help, please (2007)

    I don't see any criteria in your query.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions. (It has to be an .mdb database, most Loungers including me cannot open .accdb databases)

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help, please (2007)

    Sorry, I didn't include the criteria as it didn't work. What I had was . . .
    (SELECT Max(t.[Placement Date]) FROM [Client] AS t WHERE t.[Unique Identification Code] = [Client].[Unique Identification Code])

    I've attached the DB so you can see the structure, etc.

    Thanks, as always, for your help. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Files Attached Files

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

    Re: Crosstab query help, please (2007)

    The problem was that your clients table has a composite primary key (i.e. a primary key consisting of two fields; I hadn't anticipated that). You'll have to create two queries: one to return the most recent placement date per client, and another one to return the corresponding status.
    See the attached version.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Crosstab query help, please (2007)

    Thanks Hans. That looks great and seems to be working OK for me. If I have any problems, I'll post back, but knowing your skill, I'm sure that's it solved.

    Thank you so much, as always, for your kindness, help, and genius.

    Take care

    Brian

Posting Permissions

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