parameter query with simple outputs achieved but require a multi-field output.

[TonyJH]

Hi.
I'm new to this database world. Just started Access training a few months ago and have a somewhat complicated database already built. In a nutshell, I have a select query that uses a parameter callout so the user can enter a serial number of a widget and get test information for a report. The query pops out the test data in, for simplicity, in two columns, rest result Type A and Type B. The challenge for me is getting the results broken down into more columns. See, Type A testing is taken several times at different stages followed by Type B, the again followed by more Type A tests. The conditions of A and B tests are distinguished by TypeAID and TypeBID autonumbers which I can relate to operators, machines and conditions.

The other variation in the data is that widget 1 might have 2 As followed by 2 Bs then 1 A test as the norm. But occasionally, there is a need to retest so some widgets will have multiples of any of the Tests. Also, each test A and B are done in triplicate or more depending on the widget part number and all need to be reported.

For a single parameter entry of a SN, I'd like to be able to make a report that lists all these data in one column for each test condition and left to right by date. How is this type of operation done? Are there any special techniques I need to learn like macos or SQL code to get this done so its simple for the user? THANKS for any guidance. I hope I was clear enough to get a meaningful response.

TonyJH

[nico5038]

Not exactly sure what your problem is.
Looks to me that when you have a "TestID" and a date/timestamp for the TypeA end TypeB tests, you can report all needed. (I assume the same widget can be tested in several occasions, thus the "TestID" that identifies a single testrun)

The only problem will be the reporting on a single line.
As you'll probably not able to control the number of tests on one day, it's hard to impossible to know howmany fields are needed...
Personally I prefer to report as much as possible on separate lines.
The alternative would be to create a query with a sequencenumber per day and use that in a crosstable query to get multiple columns. This requires however a max number of tests per day and VBA coding :-(

Nic;o)

[TonyJH]

Thanks for your response.

Most of the time, testing is only done once a day but where multiple testing is likely, I would use time of day also. I think a maximum number of columns might be an acceptable way to go about accomplishing this task.

Now, what's the best way to learn about VBA coding that I'm apparently going to need to use to make this happen? I need to learn this fast or perhaps be able to "buy" a consultant's time to complete this job. Is there a "VBA code for Dummies" self-help out there that you can suggest for neophytes like myself?

[nico5038]

Here's some code I use myself for filling a report from a crosstable query:

Making the columnheader and detaildata flexible needs some VBA code in the OpenReport event.

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
 
Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
 
'Place headers
For intI = 3 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI
 
'Place correct controlsource
For intI = 3 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI
 
End Sub
 

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.

Finally you'll need to create a crosstable query based on another query that will count the number of entries per date.
To do that use the DCOUNT() function like:
select dcount("*","tbl X","datefieldna me=#" & [datefieldname] & "# and ID<=" & ID) as SequenceNumber, .....
The ID needs to be replace with the unique key of your table.

Take your time to grasp this and let me know when and where you get stuck.

Nic;o)

[TonyJH]

Thanks for your reply, Nico.

I will study your last response and try to implement a solution to the problem I posed. I'll assure you I'll get back to you when I need more help. Meanwhile, is there a VBA for Dummies or equivalent that will catapult me to more understanding in writing or understanding this code? Perhaps that has been asked in previous messages. I'll search the archives. If someone finds it, please let me know.

Thanks again.

Tony

[nico5038]

Almost any book on VBA will do, but checking out the samples on sites (like www.mvps.org/access are also a good way to start.
Finally the Access programmers guide that comes with Access and/or the Help file (press F1) are also a valuable source.

Success !

Nic;o)