Help to setup form

  • Thread starter Thread starter Toan Chau
  • Start date Start date
T

Toan Chau

Hi :-)

I have a table like this...

CSR Date Data1
--- ---- -----
BDE 10/11 8
CSS 10/11 4
ERR 10/11 3
DST 10/11 7
BDE 10/12 8
CSS 10/12 2
ERR 10/12 1
DST 10/12 3

(Thanks for listening this far)

Now, I would like to make a form that can separate the
data by the date. So it would look something like this...

CSR (Data for 10/11) (Data for 10/12)
--- --------------- ---------------
BDE 8 8
CSS 4 2
ERR 3 3
DST 7 3


Currently, I have a form that prompts me for the date, and
when I enter a date it gives me the data for all CSR's
Data for that date. But I would like for the form to give
me data for five days after the date I inputed. Something
similar to what I have listed above. But for 5 days
instead of two.

Any ideas how I should setup my form for that type of view?

A thousand thanks in advance.
-Toan
 
What you are asking for is called a Crosstab query.

1. Create a new query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.
Access adds Crosstab and Total rows to the grid.

3. Drag CSR into the grid.
Choose Row Heading under Crosstab.

4. Drag the Date field into the grid.
Choose Column Heaing under Crosstab

5. Drag Data1 into the grid.
Choose Value under Crosstab, and Sum for Total.

6. Drag the Date into the grid again.
Choose Where in the Total row.
In the Criteria row, enter:
Between [StartDate] And ([StartDate] + 5)

7. From the Query menu, choose Parameters.
In the dialog, enter the name of the parameter and its type:
StartDate Date/Time

The query now shows each heading (once) as a row, each of the 5 dates as a
column, and the value at the intersection of the matrix. If there happens to
be two "BDE" entries for the same date, it adds the two and shows the result
at the intersection point.

Since the data is grouping by the Date field, you will get too many columns
if the field contains a time component as well as a date.

Hopefully your field is not really called "Date". That is a reserved word in
VBA. While it probably won't cause a problem with this query, it will cause
you grief at some time if you use this field name.
 
Back
Top