need report showing subform details in spreadsheet format

[punderful]

I have to create an spreadsheet style report that takes data from a sub table like so:

Each owner has a main record and sub records for each animal:
(item) birth shots spay sold
Dog dateA dateB DateC DateD
cat dateA dateB DateC DateD
bird1 dateA dateB DateC DateD
fish2 dateA dateB DateC DateD

I have to create a report that parallels an excel spreadsheet, taking specific data from this sub-table like so:

owner1
dog birth cat birth fish birth
dog spay cat spay fish spay
dog sold cat sold fish sold

owner2...

I know that I can create a query for each specific item like so (select birth date where animal=dog) but I would be overloading my database with these queries!!

In the report do I create a recordset for each control on the report that will open a recordset for each specific piece of info like so:

dim rs as recordset
dim animal as string
dim event as date

animal = dog
event = birth

set rs = db.OpenRecordset (select birthdate from tblPets where pet= animal)
set rs = nothing

I'm feeling really confused about this, will this approach work or am I "barking up the wrong tree"?

 

[mas5]

I'm confused about your question. Can you show the table structure? Then it would be easier to write a query for the report.

 

[punderful]

I realize now that I'm trying to do something in a report that really is in the query. Here's what I have:

tblProject has fields txtID, txtProjectAddress, etc.
tblChangeOrder has: txtID, txtTask, dtmDue, dtmSent, dtmPaid etc.

Typical Data in the table:
txtID txtTask dtmDue dtmSent dtmPaid
23A Noise 1/1/01 1/14/01 1/21/01
23A PCD 1/3/01 1/12/01 2/3/01
23A FCD 2/12/01 2/23/01 3/1/01
27B FCD 1/4/01 1/12/01 2/3/01
27B PhotoSim 1/4/01 1/12/01 2/3/01
Design thought: the tasks involved in a project is unique to the project i.e. not all projects have Noise. Also, a project can only have one record for a specific task.


I can easily create a report that has the contents of tblChangeOrder in a subreport, but I have to produce a report that looks more like a spreadsheet, laid out exactly like the clients excel spreadsheet.

Here's an example of the report:

Project1:
(headers) Noise PCD FCD Photosim etc
(data) dtmDue dtmDue dtmDue dtmDue
(data) dtmSent dtmSent dtmSent dtmSent
(data) dtmPaid dtmPaid dtmPaid dtmPaid

Project2:
etc

Here's how I see the query layout:
txtID (PCD) (PCD) (PCD) (FCD) (FCD) (FCD) (etc)
dtmDue dtmSent dtmPaid dtmDue dtmSent dtmPaid

So I know I need a function to use in each field (except txtID) where I send in the type of document and the type of date to get a recordset that is only (example) PCD dtmDue.

Here's what I have for code, but let me start by saying that I get an error message: "user defined type not defined"
that points to the line "dim db as database". (Apparently there is no database object I can select for database, but I've seen it in code examples??)

Public Function GetRS(strTask As String, strDateType As String) As Date

'Creates recordset for reporting based on user criteria
Dim db As database
Dim rs As Recordset
Dim strSQL As String


strSQL = "SELECT tblChangeOrder.strID, tblChangeOrder.txtTask, " & strDateType & " FROM tblChangeOrder"
strSQL = strSQL & "where task='" & strTask & "';"

Set db = CurrentDb()

Set rs = db.openrecordset(sqlString, dbOpenSnapshot)
rs.MoveFirst
GetRS = rs!Fields(0)

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[mas5]

Punderful -

My quick answer is: have you looked into PIVOT Tables, PIVOT charts. I bring this up because you said it should like like an EXCEL spreadsheet and that is what PIVOT tables allow you to do. I haven't thought about what you said in detail - may not get a chance until tomorrow - but I did want to offer the PIVOT table advice quickly. See if that helps.

- MAS

 

[mas5]

Punderful -

First, maybe an answer.

if using dao

Dim db as dao.database.
Dim rs as dao.recordset.

If using ADO then it's all different. I've been converting to ADO. What a headache with syntax changes.

ADO:

Dim RST As ADODB.Recordset

'Declare and instantiate a recordset
Set RST = New ADODB.Recordset

'Establish the connection, cursor type,
'and lock type, and open the recordset
RST.ActiveConnection = CurrentProject.Connection
RST.CursorType = adOpenStatic
RST.LockType = adLockOptimistic

etc.,

Questions:

Your example report: what is (data), (headers) supposed to be? I'm having trouble picturing the report. It seems to go on and on horizontally. Is it better to turn it the other way so it continues vertically just like you have in your table. The way the user has it it seems to run off the page.

- mas

 

[punderful]

Your perception of the report is correct. I put (header) in there to signify a label at the top of the column, then (data) to signify the fields. To answer "why not put the data in there vertically", they want the report to look like the clients, and to be honest, I have a similar report that I have to send, if I can get this darn query working I might be able to save myself the trouble of having to enter data both in the database and in the spreadsheet I have to send each week.

The report they have makes sense, it's a timeline. If I created a field for each item I wouldn't have this problem, but I used more logical database design so I wasn't creating records with a bunch of empty fields.

One more question: if I am printing this report (with the fields I need to display for each record, then moving on to the next record, etc) does my sql in the function have to select the recordID? I would think that access would be smart enough to grab only the record out of my recordset that matches the record it is currently printing.

OK, one more question: if I am opening and closing a recordset say.. 30 times for EACH record, and the report has maybe 200 records, that's a lot of opening and closing of recordsets. Is that going to be slow, or is that something that's done by programmers who know what they are doing? (Me, I have a three year old degree that I haven't been using and no work experience in programming).

Thanks for all your help!

 

[punderful]

Hey, I'm making progress! I'm not getting any errors anymore, but I'm also getting weird data. I know I need to write more code, including error trapping. Here's what I am getting:

There are 31 records currently. When I take my SQL statement and run a query I get 4 records (records with the data I am selecting). When I run a query using my function I get 27 records, and the column with my function has results "12:00:00 AM". I changed the data type returned to string, and get nothing. Do I need to convert data somewhere in my function? I think it's weird that if you take the 31 records and subtract the 4 with data, you get 27, which is the # of records I get when I use my function.

Here's my revised code (I had to change the table and field names to the ones used in my DB at work, so the code does look different from my original).

Any suggestions?

 

[punderful]

Sorry, forgot to add the "new and improved code" to solicite help in getting the right results. I ran a query with the ID#, TaskName, DueDate, and in the fourth field my function - here's the results:

txtSiteID Document dtmDueDate RS function
SA13 NIER 12:00:00 AM
TA01 NIER 3/5/2003 12:00:00 AM
SA12 NIER 3/5/2003 12:00:00 AM
TA18 NIER 3/5/2003 12:00:00 AM

Help please, why is my function returning a time when it should be the same results as dtmDueDate (date)?

Public Function GetRS(strTask As String, strDateType As String) As String

'Creates recordset for reporting based on user criteria

Dim rst As ADODB.Recordset
Dim strSQL As String

'create sql string selecting records related to specific task


strSQL = "SELECT tblDocumentDetail.txtSiteID, tblDocumentDetail.txtDocumentName, "
strSQL = strSQL & "tblDocumentDetail." & strDateType
strSQL = strSQL & " FROM tblDocumentDetail "
strSQL = strSQL & "WHERE (((tblDocumentDetail.txtDocumentName)= '" & strTask & "'));"

'Declare and instantiate a recordset
Set rst = New ADODB.Recordset

'Establish the connection, cursor type,
'and lock type, and open the recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = strSQL
rst.Open
rst.MoveFirst

rst.Close
Set rst = Nothing

End Function

 

[mas5]

Sorry I haven't gotten back to you. Please try a crosstab query and see what results you get. You can create one with the query wizard and I think it will give you the results that you want without a lot of coding. Let me know if that works.

 

[mas5]

Sorry, didn't finish my thought - then use the crosstab query as the basis for your report.