merging queries?

[razoRjaw]

Bear with me if I don't explain this clearly...

I have a table with 5 check boxes in each record.
I currently have 5 queries and 5 reports, each query checks if one of the 5 checkboxes are true.

this works well, but if record #3 has chkA and chkC = true, then my reports will show record 3 two times when i print out all 5 reports under each other on a long report. is there a way to have one query which scans for TRUE on chkA OR chkB OR chkC etc...
and then i would only have one report instead of 6 total also.

i cant find anything on the web for this, but maybe thats bc i don't know what to call it

access 2010

 

[highandwild]

So if chkA and chkC are both true then which report should the record show up on, report A or report C?

 

[razoRjaw]

as it is now, if record#3 has chkA and chkC both true, rptA will show record#3 and rtpC will show record#3.
and if i print each report out one after the next i get record#3 twice overall.

im trying to prevent this, and also prevent writing 14 queries and 14 reports. if one record has 5 checks, i will end up seeing it 5 times the way i have it set up now.

not exactly the most desirable way to print a report consisting of 165 records with multiple checks...

 

[highandwild]

You have to take it step by step and not rush on.

Going back to my question:

So if chkA and chkC are both true then which report should the record show up on, report A or report C?

This is fundamental to the success of this if you do not want the same record appearing on more than one report.

Decide what each report represents and then select the records accordingly.

 

[razoRjaw]

ok, then lets have qryOne (and rptA) look for chkA OR chkC.
if i set both to true will it return a result ONLY if BOTH are checked?

right now i have chkA, B, C in qryOne but only TRUE under chkA, the rest are blank, just included to my report will show the boxes.
thanks for helping me think through this.

 

[highandwild]

I need to go to bed now, UK election or not, but you still need to decide what each report represents before you start working out queries, check boxes and whatever.

 

[razoRjaw]

what would be ideal is to have ONE report that represents all checkboxes, and ignores records that have NO checkboxes. is that a cleaner way to describe it?
thx

 

[highandwild]

It depends on what the reports represent.

We do not know the nature of the application and the relationships between the check boxes so it is quite hard to suggest a solution.

What message are you trying to get accross to the report recipient?

You seem to be are concentrating on technical and implementation issues when the business issues are not clear.

Why would report A need to consider only check boxes A and C?

 

[razoRjaw]

oh sorry, i see what you're getting at.
this is an inventory of doors that may have something wrong with them. there are 165 doors and there are 15 components that may have a problem.

I'm trying to write a query that scans the table and shows each door that does have a problem (one of the 15 chkboxes is true) but trying to prevent a door showing up twice. One report is for chkHinge, and one report is for chkKnob, and some doors have both issues true.
I have a report of the entire table, but that is 165 pages to print, and i want to print only the 25 pages that have an issue. right now i designed 15 different queries and 15 reports one for each query. the one more report which has the 15 embedded into it. really tedious and sloppy so i'd like to know a better way to accomplish this for the future.
does that help?

 

[jdraw]

Just reading this thread and understand what HighandWild was getting at.

We are guessing as to what is the business problem that the query and report
are meant to address. My interpretation is that

"We need a report that represents the doors in the inventory with one or more problems."

The report does not need to deal with those doors that are problem free.

So, looking at the door inventory, if any problem checkbox is checked, that record should be included in the report.

Is that close to what you're wanting to do?

 

[razoRjaw]

correct, guess i was thinking about it backwards

 

[highandwild]

I have attached an Excel spreadsheet that suggests a layout that may appeal to you.

This can then be replicated in Access.

 

[razoRjaw]

yes exactly, i just dont understand how to do this in a query where it doesn't require ALL "problems" to be true

 

[jdraw]

correct, guess i was thinking about it backwards

Sometimes we are too close to the problem to appreciate the real issue.

When I saw that you had 165 doors and 165 page report, I started to question the approach.

What are the fields in your table? Perhaps we can set up a query to get the data you want, and then you can build the report based on the query.

 

[highandwild]

yes exactly, i just dont understand how to do this in a query where it doesn't require ALL "problems" to be true

You said earlier:

I have a table with 5 check boxes in each record.

But there are 15 potential problems for each door.

Does each record represent a door and if so how do you log the other problems?

Can you post a database with this table in it?

 

[razoRjaw]

yeah, yesterday it was only 5, this morning we figured out it needed to be 12 (and after this i will do something similar which will probably need 15-20)
I wasn't expecting anyone to do the work for me, just to find out information, but maybe looking at it would help.
I removed the photos so it isn't a 24MB database.
Thanks

 

[jdraw]

Many of us do NOT have acc2007. Regardless of access version, though, you have to get your requirements clearly identified and data structures set up before you start finalizing queries.

If you have something like Doors and your interested in defects, make a list of things import to your business.

Model type? Style? Size? Materials of construction?
Production run date?
Quality Inspection Done? By whom?
Defect:
Knob,
Glass,
Seal,
Hinge (upper/ lower/middle)
Water penetration,
Finish,
bla, bla, bla

Then, as far as reporting,
Who is the report for?
What are they going to do with it?
How often will they need it?

Get answers to these and the data requirements/structures and report
will get relatively simple/straightforward.