Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Crosstab query (Access 2000)

    1. I have made a crosstab query based on the following tables.

    Employee table:
    [Employee #]
    [Name]

    Availability table:
    [Employee #]
    [Date available]

    The tables are left joined on the [Employee #] field, and the results are like the following if the criteria (From date, To date) include all records in the Availability table.

    [Name][<>][1][2][3][4][5][6][7]
    Tom | | - | - | - | - | 1 | 1 | 1 |
    Dick | 0 | - | - | - | - | - | - | - |
    Harry | | - | 1 | 1 | 1 | - | - | - |

    However, if I specify a range of dates, employees who have no records in the Availability table for those dates are not included, as follows.

    [Name][2][3][4][5][6]
    Tom | - | - | - | 1 | 1 |
    Harry | 1 | 1 | 1 | - | - |

    How can I make the query include all employees, and without the "<>" column containing zeros for employees who have no records in the Availability table, as follows?

    [Name][2][3][4][5][6]
    Tom | - | - | - | 1 | 1 |
    Dick | - | - | - | - | - |
    Harry | 1 | 1 | 1 | - | - |

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab query (Access 2000)

    The <> indicates there's no date for him in one of the fields, it's a null field. You'd need to add a where clause to the SQL, WHERE Query.[DateAvailable] Is Not Null
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Crosstab query (Access 2000)

    Why is the presence of the "<>" field a problem? You can ignore it in forms and reports, and end users should never see the query itself.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    1. Thanks for the suggestion to skip this column in forms and report. How/where would I specify this?

    2. How can I make the query include employees who have no records in the Availability table when I specify a date range, as currently they are only being included if I don't specify date criteria?

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

    Re: Crosstab query (Access 2000)

    1) Don't include a text box bound to this column.
    2) What do the criteria look like now?

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    1. OK, thanks.

    2. I worked out that I needed an additional Is Null criterion in the date field in the right table to allow for records in the left table that had no record in the right table.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    I found that I only partly solved my problem with the date criteria. I only want to include dates from today to two weeks from today, but I want to list all the employees in the left table, whether they have (a) at least one record in the right table that matches the date criteria, or ([img]/forums/images/smilies/cool.gif[/img] no records at all in the right table, or records in the right table but none that match the date criteria.

    I can achieve (a) and ([img]/forums/images/smilies/cool.gif[/img] with the criteria (>=Date() And <=Date()+14) Or Is Null, but how can I include as well?

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

    Re: Crosstab query (Access 2000)

    As far as I can tell without seeing the database, you will have to create a query with the date criteria that also includes the Employee #, save it, then create a new query based on the employee table left joined to the query you just saved on Employee #. Take the Name field from the table, and the other fields from the query.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    Yes, that works OK, thanks.

    1. Because the column headings are the current date and the next 14 days (>=Date() And <=Date()+14), the headings change every day when the query is run. Will this cause a problem with labelling these fields in forms or reports based on the query?

    2. Is it possible to add a pair of parameters so that the user can specify a Start Date and an End Date to select only records where an employee is available:
    (a) on at least one date in the specified range (inclusive of the start and end dates)?
    ([img]/forums/images/smilies/cool.gif[/img] on at least 50% of the dates in the specified range?
    on the first OR the second date in the specified range?
    (d) on the last AND the next-to-last date in the specified range?

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

    Re: Crosstab query (Access 2000)

    1. Yes, you'll have to change these headings in code. Do a search in this forum for dynamic crosstab form or dynamic crosstab report.

    2. See Charlotte's reply in <post#=500,150>post 500,150</post: >.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    I searched the forum for information on dynamic field headings as you suggested, but I don't know how to program these. However, I gathered that this will work automatically if I use a datasheet form, so I tried that, as a datasheet form will be suitable for my application.

    However, when I tried to create a form using the form wizard, I selected the query that I need to use but no fields were available for selection. I then tried designing a form, but again, after I set the query that I needed as the data source, no fields were available in the form's field list. The query that I need to use is built from three other queries, and it runs fine as a query. I can use the wizard or design a form with any of the three subqueries, but not from the main query. How can I overcome this?

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

    Re: Crosstab query (Access 2000)

    A datasheet form will only work if the column headings are fixed, but as you stated, they change with the date.
    A form or report based on a crosstab query with changing column headings requires the use of non-trivial VBA code. You can find an example for forms in <post#=297,950>post 297,950</post: > and for reports in <post#=248210>post 248210</post#>, among others.

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    Thanks for your reply. I'm not sure whether you mean that a datasheet form will work if the only headings that change are dates, or if this will still need some programming, in which case I don't know how to use the information in the other posts that you cited so may not be able to create a form from this query.

    Are you able to advise briefly why no fields are available in the field list of a form based on my main query but they are available in any of the three queries on which the main query is based?

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

    Re: Crosstab query (Access 2000)

    What I meant is that a datasheet form will only work if the column headings of the crosstab query will be the same each time you run it. According to your description, this is not the case, so you will need VBA code.

    I don't know enough about your query to know why you don't see its field names when designing a form or report.

  15. #15
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Crosstab query (Access 2000)

    Thanks for your reply. My application sounds very similar to the one referred to in the post that you cite. Do I just need to create fields in the datasheet form for the date columns from the crosstab query and then copy the code from that post and paste it somewhere on the form to specify the data for the date columns, or is advanced programming knowledge required?

Page 1 of 2 12 LastLast

Posting Permissions

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