473,390 Members | 1,668 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

Dynamic Fields Report (Access 2000)

Thanks for taking the time to read my question.

I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own
column
across the top and Category down the left side.

As data is entered, the number of unique dates increases. As a result
the
number of columns increases. How do you make a report like this in
Access? I
know I can do a crosstab query, but I need to format it etc.

I tried using a basic query that uses just the Category, then adding my
crosstab query to it as a SubDatasheet. Access doesn't like that
either.

Thanks so much for you help on this.

Brad

Ex:
tblData
Date Category Data
1-1-06 Dog Food 6.5
1-1-06 Cat Food 7.2
1-1-06 Bird Seed 1.6
1-1-06 Dog Food 6.4
1-1-06 Cat Food 7.3
1-1-06 Bird Seed 1.5
2-1-06 Dog Food 6.3
2-1-06 Cat Food 7.9
2-1-06 Bird Seed 1.7
2-1-06 Dog Food 6.4
2-1-06 Cat Food 8.0
2-1-06 Bird Seed 1.9
3-1-06 Dog Food 6.9

Report:

Dog Food 1-1-06 2-1-06 3-1-06
6.5 6.3 6.9
6.4 6.4
Cat Food
7.2 7.9
7.3 8.0
Bird Seed
1.6 1.7
1.5 1.9
*** Sent via Developersdex http://www.developersdex.com ***
Feb 8 '06 #1
1 5137
You need to create a crosstab query with a fixed number of columns where the
columns are sized to hold your widest data value. The column labels need to
be named like LblDate1, Lbldate2, LblDate3 so that you can put the label
names in a loop. The column textboxes need to be named similar to the column
labels so you can put the column textboxes in the same loop as the column
labels. You need a query that only includes the Date from your table data
and the query must return unique dates. A DCount on this query or a
resordset.recordcount on this query sets the total number of columns you
need. Assuming your crosstab report is designed with eight columns, if
(total number of columns needed) Mod 8 = 0, you need to generate (total
number of columns needed)/ 8 pages. If (total number of columns needed) Mod
8 <> 0, you need to generate ((total number of columns needed)/ 8) + 1
pages.

From here you need a loop from 1 to the total number of pages. The crosstab
query needs to be inside the loop and the crosstab query needs a dynamic
criteria so the correct Dates and Data are on the page at each iteration of
the loop. A recordset needs to be built from the Date query and the
recordset needs to be in an internal loop. The internal loop loops from i =
1 to 8. In the internal loop the column label is assigned with Me("LblDate"
& i) = Rst!Date and the column textbox control source is assigned with
Me!("Data" & i) = Rst!Data. The code for the external and internal loops
needs to be in the Open event of the crosstab report. At the end of the
external loop, you need to print the report and then close the report to get
each page of the report. (Page here is not the typical definition of pages
for the report, but is one open, print and close cycle of your code. For
example, if you determine you need 5 pages, you must open, print and close
your report in the external loop five times. In effect you print the same
report over and over just changing the column labels and column data each
time you print.)

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1100 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com


"Brad" <no****@devdex.com> wrote in message
news:0p***************@news.uswest.net...
Thanks for taking the time to read my question.

I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own
column
across the top and Category down the left side.

As data is entered, the number of unique dates increases. As a result
the
number of columns increases. How do you make a report like this in
Access? I
know I can do a crosstab query, but I need to format it etc.

I tried using a basic query that uses just the Category, then adding my
crosstab query to it as a SubDatasheet. Access doesn't like that
either.

Thanks so much for you help on this.

Brad

Ex:
tblData
Date Category Data
1-1-06 Dog Food 6.5
1-1-06 Cat Food 7.2
1-1-06 Bird Seed 1.6
1-1-06 Dog Food 6.4
1-1-06 Cat Food 7.3
1-1-06 Bird Seed 1.5
2-1-06 Dog Food 6.3
2-1-06 Cat Food 7.9
2-1-06 Bird Seed 1.7
2-1-06 Dog Food 6.4
2-1-06 Cat Food 8.0
2-1-06 Bird Seed 1.9
3-1-06 Dog Food 6.9

Report:

Dog Food 1-1-06 2-1-06 3-1-06
6.5 6.3 6.9
6.4 6.4
Cat Food
7.2 7.9
7.3 8.0
Bird Seed
1.6 1.7
1.5 1.9
*** Sent via Developersdex http://www.developersdex.com ***

Feb 8 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: puunda | last post by:
Hi, Hope I've posted to the right groups. I'm trying create a Crystal Report for the first time using C# (novice at that as well). The CR I can handel. What I want to do is to have a whole...
3
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what...
5
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
0
by: CSDunn | last post by:
Hello, I have a format issue on an Access 2000 ADP report that I am going to attempt to explain from a 'ten thousand foot view' : I have an Access 2000 ADP report that has a SQL Server 2000...
9
by: mooseshoes | last post by:
All: I'm using Access 2000 on a Windows XP platform. My goal is to use a form to gather user criteria which I will then parse into a useable SQL string. At this point I would like to open one...
2
by: Darryl Kerkeslager | last post by:
As the subject above hopefully makes clear, I want to do several reports, "with lots of fields not otherwise in database". These reports also have variable-length text. I have defined the...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.