Crosstab query (Access 2000)

**Murgatroyd** · 2005-07-14 17:56

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 | - | - |

**NYIntensity** · 2005-07-14 18:39

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

**HansV** · 2005-07-14 19:48

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.

**Murgatroyd** · 2005-07-15 05:45

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?

**HansV** · 2005-07-15 05:48

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

**Murgatroyd** · 2005-07-15 19:08

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.

**Murgatroyd** · 2005-07-15 21:15

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?

**HansV** · 2005-07-15 21:26

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.

**Murgatroyd** · 2005-07-18 04:14

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?

**HansV** · 2005-07-18 04:45

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: >.

**Murgatroyd** · 2005-07-20 20:24

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?

**HansV** · 2005-07-20 21:16

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.

**Murgatroyd** · 2005-07-21 07:23

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?

**HansV** · 2005-07-21 07:35

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.

**Murgatroyd** · 2005-07-21 13:03

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?

Thread: Crosstab query (Access 2000)

Thread Tools