S
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
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