A dynamic table in a report. Is it possible?

[Monkey_O]

I have a table with three columns, two of which are a joint primary key. The
primary key consists of a column with a date, and the other is a column for
an employee ID (there is a seperate table that stores all the employee's
information). The third column simply contains a number.

What I want is a report where it's kind of like a table: across the top are
all the employees, down the left are the dates, and where the two values join
up is the number. Basically, it will look like an excel table.

Is this possible?

 

[Duane Hookom]

Have you looked at creating a crosstab query?

 

[Monkey_O]

I have no idea what a cross tab query is. Never even heard of the term
before, actually.

I'll google it up right now, though.

 

[Duane Hookom]

Why google it when you can use Access Help. You can also open the Northwind
sample and find the query "Quarterly Orders by Product". Reporting crosstabs
can be a bit tricky if you don't know the column headings ahead of time.
Come back if/when you have questions.

 

[Monkey_O]

Good news. I got the query displaying correctly, which was a little tricky,
as I had to do a couple of joins and make a few extra queries to get data to
show up properly.

Now I just need to try to see if I can get it to display in a report. If
not, I suppose I could do a screen capture or a subform in a report.

I'll update in a bit,

 

[Monkey_O]

Alright, I have been playing around using a form with my cross tab query, and
it displays fine now in a form at the moment. The problem is, if I were to
add a new employee, it would not show up on the form. Since my employees are
the column headers, the form won't dynamically draw a new column for me,
meaning that whenever I want to add a new employee, the form needs to be
modified.

The only way I can see this being solved is going into code, looping through
the query, and create a new label for each column. This would be the same for
both a report and a form.

Is there an easier way to do this?

 

[Duane Hookom]

There are some sample crosstab reports at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Monkey_O]

I just realized that it might be a lot easier to just take a snapshot, so to
speak, of the cross tab query tabke, and put that in the report. The table
for the cross tab query dynamically adds columns for me.

Is this a better solution? Or is it even possible?

 

[Duane Hookom]

I don't know what you mean by "take a snapshot".

 

[Monkey_O]

I mean display the datasheet results of the crosstab query in a report. I
know you can paste sub forms or sub reports, but can you do the same with
datasheets?

 

[Duane Hookom]

I doubt it.

--
Duane Hookom
MS Access MVP

I mean display the datasheet results of the crosstab query in a report. I
know you can paste sub forms or sub reports, but can you do the same with
datasheets?