Stymied by problem with report and underlying query

[Hilary Ostrov]

Using Access 2000 ... I have a very simple report that I would like to
use for two purposes. One is as a "blank" with basic information
(taken from form in unbound controls on the report) in the Page
Header. So far, so good - no problems.

But if I add a second use for this report, i.e. to fill in the
"blanks" for which data might have been entered, it only works for the
first record. Even if I manually run the query with record 2 showing,
and then run the report, I still get all blanks!

Here are the tables and the SQL for the report query:

tblIndexContents:

IndexContentID Autonumber (PK)
Division Text
Item Text

tblProjSuppliers:

ProjSupplierID Autonumber (PK)
ProjectID Number
IndexOrder Number
Division Text
Item Text
Supplier Text
SupplierContact Text
ContactPhone Text
ContentDetail Yes/No

[As an aside, I'd be interested in knowing if there's a better way to
address the issue of having to store Division and Item in
tblProjSuppliers because some "Items" can appear in more than one
Division. I'm sure I must be breaking a cardinal rule of table
normalization, but as a relative newbie still, this seemed like the
quickest way to ensure that the user will be able to select first the
Division, and then the Item from combos. I suspect if I understood
arrays and how to get them to work, that this and unbound combos would
solve the problem without a huge amount of coding. But I don't ]

SELECT tblIndexContents.Division, tblIndexContents.Item,
IIf([Forms]![Projects]![ProjectID]=[ProjectID],[Supplier],"") AS
PSupplier,
IIf([Forms]![Projects]![ProjectID]=[ProjectID],[SupplierContact],"")
AS Contact,
IIf([Forms]![Projects]![ProjectID]=[ProjectID],[ContactPhone],"") AS
Phone
FROM tblIndexContents LEFT JOIN tblProjSuppliers ON
(tblIndexContents.Item = tblProjSuppliers.Item) AND
(tblIndexContents.Division = tblProjSuppliers.Division)
WHERE (((tblIndexContents.Division)<>"Directories"))
ORDER BY tblIndexContents.Division, tblIndexContents.Item;

This does create a problem of duplicating tblIndexContents.Item in
instances where there is data in PSupplier, SupplierContact & Phone -
which strongly suggests there may well be a problem with my query;
however, I was able to workaround this by using a header grouping for
"Item" in the report - and placing all relevant fields in this header.
FWIW, "Division" is also a group header.

Should I abandon the idea of creating a report which lists *all*
divisions and items _and_ data for those divisions and items for which
data exists? Or is there something I can do to make this work?! Of
course, there are other tables, forms and reports in the .mdb, but
they all work fine!

Many thanks in advance for any assistance.
hro

 

[Hilary Ostrov]

On Mon, 26 Jul 2004 02:16:43 GMT, in
<[email protected]>, Hilary Ostrov

[...]

But if I add a second use for this report, i.e. to fill in the
"blanks" for which data might have been entered, it only works for the
first record. Even if I manually run the query with record 2 showing,
and then run the report, I still get all blanks!

Here are the tables and the SQL for the report query:

[...]

I went back to the (query) drawing board, and got it to work! All I
needed to do was first query tblProjSuppliers setting
Forms!Projects!ProjectID as the criteria for ProjectID, then a second
query:

SELECT tblIndexContents.Division, tblIndexContents.Item,
ItemsForDraft.PSupplier, ItemsForDraft.SupplierContact,
ItemsForDraft.Phone
FROM tblIndexContents LEFT JOIN ItemsForDraft ON
(tblIndexContents.Item = ItemsForDraft.Item) AND
(tblIndexContents.Division = ItemsForDraft.Division)
WHERE (((tblIndexContents.Division)<>"Directories"));

This way, I succeeded in killing 2 bugs with one query, so to speak -
because I no longer get the duplicates mentioned in my first post, and
I get "blanks" when I want 'em, and data when it's there. I realize
this may not be the *best* way to get where I wanted to go, but it
seems to work

[...]
hro