Report Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following fields: Document1IfRequired (text),
Document1Rec'd (yes/no), Document1Rec'dDate (date). There are 6 additional
fields which are for Document 2 & 3. I run a Missing Documents report based
on a query that has the criteria of: Document1IfRequired is not null and
Document1Rec'd is no OR if Document2IfRequired is not null and Document2Rec'd
is no OR if Document3IfRequired is not null and Document3Rec'd is no. This
lets me know which documents I'm missing of those that are required.

This is my problem: If Document 1, 2 & 3 are all required and I've received
say, 1 & 2, I want my report to only contain the Document3IfRequired field.
Right now it shows all 3 documents whether they have been received or not.
As a quick fix I added the Document1Rec'dDate (2 & 3 also) so at least it
would show up that they were received, but it appears cluttered and is
sometimes confusing to the recipient of the report. Can I make those lines
invisible if the documents have already ben received?
 
What are you going to do with your table and all the queries, forms, and
reports based on it when someone changes the business rules and adds a
Document4?

That problem and the one you describe now are due to your table not being
properly normalized. You are trying to "commit spreadsheet". Your table has
serious normalization problems and just will NOT work correctly in a
relational database. Period.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
Thank you Jerry for your most humble opinion but that really doesn't solve my
problem. I have asked other questions within this forum before and you are
the only one that felt entitled to insult my intelligence for some reason.
Now, with that being said, short of rewriting my database, can anyone suggest
criteria I can use for my current dilemna?
 
I don't think that Mr Whittle was trying to insult you. He gave you good
advice about redesigning your query and seemed to think that you were
capable of following it.

You really do need to redesign your table structure, BUT if you can't (or
choose not to) then you can use a "normalizing" union query to return the
data for a report. UNION queries can only be constructed in the SQL view
and not in the grid (design) view. The SQL for that query would look
something like:

SELECT "Doc1" as DocPosition
, Document1IfRequired
, [Document1Rec'd]
, [Document1Rec'dDate]
FROM [Your Table]
WHERE Document1IfRequired is not null and
[Document1Rec'd] = False
UNION
SELECT "Doc2" as DocPosition
, Document2IfRequired
, [Document2Rec'd]
, [Document2Rec'dDate]
FROM [Your Table]
WHERE Document2IfRequired is not null and
[Document2Rec'd] = False
UNION
.....
UNION
SELECT "Doc6" as DocPosition
, Document6IfRequired
, [Document6Rec'd]
, [Document6Rec'dDate]
FROM [Your Table]
WHERE Document6IfRequired is not null and
[Document6Rec'd] = False

You should be able to use that query as the source for your report. Note
that this query is not updatable, may be slow, and will have to be rewritten
in you ever have to add Doc7 or Doc8 to the table. If it doesn't return all
the information you require, then use it as a model - and look up UNION
query in the help.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Sorry if I came across a little harshly. I still stand by what I said, but
maybe not how I said it.
 
Minor correction

.... He gave you good
advice about redesigning your ***table structure*** and seemed to think that
you were
capable ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
I don't think that Mr Whittle was trying to insult you. He gave you good
advice about redesigning your query and seemed to think that you were
capable of following it.

You really do need to redesign your table structure, BUT if you can't (or
choose not to) then you can use a "normalizing" union query to return the
data for a report. UNION queries can only be constructed in the SQL view
and not in the grid (design) view. The SQL for that query would look
something like:

SELECT "Doc1" as DocPosition
, Document1IfRequired
, [Document1Rec'd]
, [Document1Rec'dDate]
FROM [Your Table]
WHERE Document1IfRequired is not null and
[Document1Rec'd] = False
UNION
SELECT "Doc2" as DocPosition
, Document2IfRequired
, [Document2Rec'd]
, [Document2Rec'dDate]
FROM [Your Table]
WHERE Document2IfRequired is not null and
[Document2Rec'd] = False
UNION
....
UNION
SELECT "Doc6" as DocPosition
, Document6IfRequired
, [Document6Rec'd]
, [Document6Rec'dDate]
FROM [Your Table]
WHERE Document6IfRequired is not null and
[Document6Rec'd] = False

You should be able to use that query as the source for your report. Note
that this query is not updatable, may be slow, and will have to be
rewritten in you ever have to add Doc7 or Doc8 to the table. If it doesn't
return all the information you require, then use it as a model - and look
up UNION query in the help.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Sandra P said:
Thank you Jerry for your most humble opinion but that really doesn't
solve my
problem. I have asked other questions within this forum before and you
are
the only one that felt entitled to insult my intelligence for some
reason.
Now, with that being said, short of rewriting my database, can anyone
suggest
criteria I can use for my current dilemna?
Click to expand...
Click to expand...
 
Back
Top