selecting Fields for Report

[paul.d.barnes]

ACCESS 2002

I have a query with the main fields being Region, System1,
System1Comments, System2, System2Comments, System3, System3Comments,
....System6, System6Comments

The user is to previews a report based on two criteria on a form, a
value from the Region and the System. To select the region I'm using,
DoCmd.OpenReport stDocName, acPreview,, "[RegionID]= " &
Me.RegionID & ""
in the first combo box And that works ok, my question is if the user
selects (for example) system2 from the second combo box on the form,
how do I get all records for just Fields System2 and System2Comments to
show up on the report?

 

[Duane Hookom]

I would create a union query to normalize the table so you could use
standard sql statements.

SELECT Region, System1 as System, System1Comments as Comments, 1 as
SystemNum
FROM tblSpreadsheet
UNION ALL
SELECT Region, System2, System2Comments ,2
FROM tblSpreadsheet
UNION ALL
SELECT Region, System3, System3Comments ,3
FROM tblSpreadsheet
UNION ALL
--etc---
You can then select the Region and the SystemNum from your union query for
your report.

 

[DannyOcean]

Thanks for the help!
but it seems I can only create a union for the first sql statement
(system1)
I keep getting errors 'Characters after end of sql staement' or syntax
errors
if i try to add the additional unions

any ideas?

 

[Duane Hookom]

When you get an error creating a query, please paste the SQL view into a
reply so we can help you.

 

[DannyOcean]

here is my latest sql statement with just two systems (fileds)

SELECT ChartInfo.ChartID, ChartInfo.[Chart Number], ChartInfo.RegionID,
MainTest.TestID, MainTest.TestChartID, MainTest.TestCategoryID,
MainTest.System1 as System, MainTest.System1Comments as Comments, 1 as
SystemNum
FROM ChartInfo INNER JOIN MainTest ON ChartInfo.ChartID =
MainTest.TestChartID
UNION SELECT ChartInfo.ChartID, ChartInfo.[Chart Number],
ChartInfo.RegionID, MainTest.TestID, MainTest.TestChartID,
MainTest.TestCategoryID, MainTest.System1 as System,
MainTest.System1Comments as Comments, 1 as SystemNum
FROM ChartInfo INNER JOIN MainTest ON ChartInfo.ChartID =
MainTest.TestChartID
SELECT ChartInfo.ChartID, ChartInfo.[Chart Number], ChartInfo.RegionID,
MainTest.TestID, MainTest.TestChartID, MainTest.TestCategoryID,
MainTest.System2 as System, MainTest.System2Comments as Comments, 2 as
SystemNum
FROM ChartInfo INNER JOIN MainTest ON ChartInfo.ChartID =
MainTest.TestChartID
UNION SELECT ChartInfo.ChartID, ChartInfo.[Chart Number],
ChartInfo.RegionID, MainTest.TestID, MainTest.TestChartID,
MainTest.TestCategoryID, MainTest.System2 as System,
MainTest.System2Comments as Comments, 2 as SystemNum
FROM ChartInfo INNER JOIN MainTest ON ChartInfo.ChartID =
MainTest.TestChartID


what ever could I be missing,
Thanks for the help

 

[Duane Hookom]

Your third SELECT doesn't have a UNION preceding it. Also, once you alias a
column in your first select, you don't need the " As SystemNum" in the
following SELECTs.

 

[DannyOcean]

That Works! Thanks, you've been a Great Help.
I just realized that this quary will have some 200 chartids X 27 test X
7 systems = 37800+ Records,
what kind of effect will that have on the quary and/or Report?

 

[Duane Hookom]

Normalization would have been much advised. You might get better performance
if you use UNION ALL rather than just UNION.

 

[DannyOcean]

UNION ALL does work bitter, can't believe I missed that one,
On my form in the combo box, how can I select the SystemNum while
displaying the name of the system for the user to select?

 

[Duane Hookom]

Aren't these static values since they came from field names? You might be
able to use a value list combo box with two columns, bound to the first
column and column widths of 0";1"
Row Source: 1,"First System",2,"Second System",....

 

[DannyOcean]

The column is populated with the list from the row source but I'm
unable to select any of the values.

 

[Duane Hookom]

Do you have anything in the combo box control source? You shouldn't.

 

[DannyOcean]

heres what I have,
Control source = unbound
Row Source type value list
row source 1,system1,2,system2...

Form Record Source = the union quary
allow edits no
allow deletions no
allow additions no
data entry no
recordset type dynaset
record locks no locks

I've try many combinations for the form values but none seem to work.
i've also noticed that i'm unable to edit the records in the union
quary, i'm guessing
thats ok.

Thanks!

 

[DannyOcean]

Sorry, all is working now, just created a new form from the query and
everthing populates as it should.

Thanks again for all the Help!
P