Hi,
Please see the attached file, which tells a big part of my story. The numbers represent the number of folks who answered "Y" to each question, based on race and gender. There could be thousands of lines of data as well as a few more race/gender combinations.
I'm looking for an efficient way to get from the data example to the report. I know I can create a table that looks like the report example the hard way (an update query for every race/gender combination). From there I can build the report but there has to be a better way.
I need help with either an easier way to get the data in a format similar to the report example or to skip the whole reformat step and go straight to the report. I can imagine the first could be done but, although the second would be preferable, I'm not smart enough to know if it's even doable.
I'd appreciate any help you could offer!
Thanks,
Shawn
5 916
As I couldn't open your file, I have no idea what you want. Perhaps if you tried zipping it and re-sending someone might be able to help.
Phil
Shawn29316,
Is there any reason that you need to execute an update query?
What prevents you from building the required view of your database, and then use that as the basis of your report?
Alternately, as a view is just a (sometimes updatable) query, you can just build your query as a stored query and base the report on that.
Or, am I missing something obvious?
Cheers,
Oralloy
Oralloy,
I noticed by your name there's an "expert" tag. That obviously doesn't describe me so there may be a simple way to do this which I just don't know.
My logic for the queries was to use one to create a table containing the question number and a blank field for each race/gender combination. Then there would be an update query for each column to add the data for each race/gender combination.
Thanks for you willingness to help!
Shawn
I think this might help.
Table 6 is made by copying your Excel file into a new table.
table 7 must be defined exactly as this image
Then use the following code -
Option Compare Database
-
Option Explicit
-
-
Function ChangeLayout()
-
-
Dim MyDb As Database
-
Dim InSet As Recordset, OutSet As Recordset
-
Dim i As Integer
-
Dim TestInt As Integer
-
Dim FieldValue As Variant
-
Dim FieldName As String
-
-
On Error GoTo ChangeLayout_Err
-
-
Set MyDb = CurrentDb
-
Set InSet = MyDb.OpenRecordset("SELECT Table6.* FROM Table6;")
-
Set OutSet = MyDb.OpenRecordset("SELECT Table7.* FROM Table7;")
-
-
With InSet
-
Do Until .EOF
-
OutSet.AddNew
-
For i = 1 To .Fields.Count - 1 ' ignore first field (ID in new table)
-
FieldValue = .Fields(i)
-
If Left(FieldValue, 8) = "Question" Then
-
FieldName = "Question"
-
FieldValue = Mid(.Fields(i), 10) ' Number after question
-
Else
-
FieldValue = .Fields(i)
-
OutSet.MoveLast
-
OutSet.Edit
-
End If
-
TestInt = CInt(FieldValue) ' Gives an error for the field name fields
-
OutSet.Fields(FieldName) = Nz(FieldValue)
-
OutSet.Update
-
NextField:
-
Next i
-
.MoveNext
-
OutSet.Requery
-
Loop
-
.Close
-
Set InSet = Nothing
-
End With
-
-
-
ChangeLayout_Exit:
-
Exit Function
-
-
ChangeLayout_Err:
-
If Err = 13 Then ' Failed to convert text to number
-
FieldName = FieldValue
-
Resume NextField
-
ElseIf Err = 3020 Then ' No value
-
Resume NextField
-
Else
-
MsgBox Err & " " & Err.Description
-
End If
-
-
End Function
-
-
Phil
Shawn29316,
Does PhilOfWalton's example get you off of the ground?
Oralloy
Sign in to post your reply or Sign up for a free account.
Similar topics
by: osgnamah |
last post by:
Hi All;
I am getting ready to launch a shareware program in the next few weeks
and the last step is going to be creating a website. So the last few
days I've been doing some web surfing in...
|
by: |
last post by:
Hello.
As I know the crystal report is part of visual studio.
1.When I open a new project I don't have the Crystal
Report option. I have
C#,C++,V.B,Setup & deployment and visual studio solutions,...
|
by: AstronusX |
last post by:
Hi, I'm looking for good printing/reporting tool that customize base on user
need, like add more columns to report(design at run-time) for the products
that we make? Any recommendation??
Thank...
|
by: Wayne Aprato |
last post by:
I've just spent about an hour reading posts on exporting reports as
snapshots but still can't find a solution to my exact problem.
If I am looking at a report in preview mode and want to click a...
|
by: Brenda |
last post by:
I would like to know how to make a report longer than the default 2 1/2
pages. Can this be done with sub reports? I would like to be able to
make a 15 page report previewable. Thank you for all...
|
by: mvsguy |
last post by:
I'm an Access noob and apologize if I'm asking a stupid question. The
problem is that I'm unable to design/preview reports on a database.
How do I go about fixing this?
Background -
A user has...
|
by: BrianDH |
last post by:
Hi
I am working on a C# Windows Application that builds an invoice (CR)
converts to PDF and attach to email one at a time.
This works untill my invoice count get over 40 to 50 invoices then I...
|
by: Orchid |
last post by:
Hello all,
I have different version of reports which used for different months.
For example, I am using report version 1 up to September, but we have
some design changes on the report for October,...
|
by: jambonjamasb |
last post by:
Hi
I have two tables:
email_tbl
Data_table
Data table is is used to create a Form
Data_form
|
by: Sean Tech |
last post by:
Hello,
I am currently working on a sales tracking report and could use some assistance. Currently I have a form that the user opens and picks a week date to view the report. In the form there is...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
|
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...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |