query with null values

[snelson via AccessMonster.com]

I have an access 2003 db and I wish to produce a report.

The report is based on one record and its information.

The information is contained in the 'main table' and some of this information
is obtained by selecting from a cbo attached to a 'look up table.'

the' lookup table' as an 'itemid' and several other pieces of info in it.
Once selected on the 'itemId' is stored in the 'main table.'

In my query for the report I have the maintable and the lookup table joined
to give me the information I require.

My problem (at last) is that a person may have up to 4 of the items listed in
the lookup table, I have created 4 cbo's so that I can select all or any of
the items a person has. In the 'maintable' I included fields 'item2id,
item3id and item4id'

This is where I am losing it......I go to the query in the report and as
described above, I get the result with the first item. How I get the other 3
(if there is any)

If I add the lookup table to the query 4 times and connect

itemid to the maintable itemid
Item2id to the maintable itemid
item3id to the maintable itemid
item4id to the maintable item id

It will return the result with all 4 items if they exist.

My problem is, if items 2,3 or 4 do not exist the query will not return
anything. (it wants all 4 to be populated).

I have been through a number of the posts and have tried many of the formulas
given but to no avail.

can anyone help give me a clue how to solve this.

I want the report to return the information if there is a value in itemid
with nothing in item2id, nothing in item3id and nothing in item4id.

return the information if there is a value in itemid and item2id with nothing
in item3id and nothing in item4id.

return the information if there is a value in itemid, item2id and item3id and
nothing in item4id

and return the information if there is a value in all of the itemid fields.

Not sure if this is making sense (even I am having trouble with it)

Thanks in advance

Steve

 

[Guest]

Did you check out any of the posts in these news groups that suggest 4 item
fields is not a very good table structure? Normally you would want 4 records
in a related table rather than 4 fields.

You can however change your joins to include all the records from the "main
table" by view and updating the join properties in your query.

 

[Marshall Barton]

I have an access 2003 db and I wish to produce a report.

The report is based on one record and its information.

The information is contained in the 'main table' and some of this information
is obtained by selecting from a cbo attached to a 'look up table.'

the' lookup table' as an 'itemid' and several other pieces of info in it.
Once selected on the 'itemId' is stored in the 'main table.'

In my query for the report I have the maintable and the lookup table joined
to give me the information I require.

My problem (at last) is that a person may have up to 4 of the items listed in
the lookup table, I have created 4 cbo's so that I can select all or any of
the items a person has. In the 'maintable' I included fields 'item2id,
item3id and item4id'

This is where I am losing it......I go to the query in the report and as
described above, I get the result with the first item. How I get the other 3
(if there is any)

If I add the lookup table to the query 4 times and connect

itemid to the maintable itemid
Item2id to the maintable itemid
item3id to the maintable itemid
item4id to the maintable item id

It will return the result with all 4 items if they exist.

My problem is, if items 2,3 or 4 do not exist the query will not return
anything. (it wants all 4 to be populated).

Change the type of the joins to include all the records from
the main table and any matching records form the other
table.

 

[John Spencer]

Just in case you don't understand how to change the joins.

In query view,
-- Double click on the join line
-- Select the option that shows all records in the main table and only
matching records in the "Lookup" table
-- Repeat for the other three joins.

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

 

[snelson via AccessMonster.com]

Thank you to all who pointed me in the right direction, it actually works the
way I want it to now.

That is pretty good, given my round about way of describing the problem and
more than likely my incorrect use of descriptions.

Thanks again

Steve

Just in case you don't understand how to change the joins.

In query view,
-- Double click on the join line
-- Select the option that shows all records in the main table and only
matching records in the "Lookup" table
-- Repeat for the other three joins.
[quoted text clipped - 38 lines]

the main table and any matching records form the other
table.

 

[snelson via AccessMonster.com]

Duane, thanks for the heads up, i will check out the posts and try to figure
out my structure problems, using the joins I got it working the way I wanted
but I would still like to get a handle on this structure idea (all new to me).


Once again, thanks

Steve

Did you check out any of the posts in these news groups that suggest 4 item
fields is not a very good table structure? Normally you would want 4 records
in a related table rather than 4 fields.

You can however change your joins to include all the records from the "main
table" by view and updating the join properties in your query.

I have an access 2003 db and I wish to produce a report.

[quoted text clipped - 51 lines]

Steve