S
Steven
** I am trying to create a listing report based on 2 search criteria
(potentially linked to separate queries). This report is based on 2 linked
tables. The question is as short as it can be while maintaining the requisite
level of detail. For the bottom line of the summary, scroll to the bottom of
this page.
I created a database with 2 linked tables. The intention is to compare
prices and aisle numbers at different supermarkets, based on a selection of
products.
TABLE_PRODUCT
idProduct - Autonumber
Product
Type
Selected
TABLE_LOCATION_PRICE
idLocation - Autonumber
idProduct - Number (linked to TABLE_PRODUCT)
Supermarket
Brand
Aisle
Price
FORM_PRODUCT_SELECTED
I created a form that provides a list of all available products (from
TABLE_PRODUCT), with check boxes next to them. The output of the check boxes
is stored in TABLE_PRODUCT.Selected. Only a positive selection will record
"-1" in this field.
QUERY_PRODUCT_AND_LOCATION
I linked TABLE_PRODUCT with TABLE_LOCATION_PRICE, in a one-to-many
relationship, where every product is selected, and every supermarket matching
each product is selected. Where there is no Supermarket to match a Product,
the Product is still listed with a NULL entry in the Supermarket field).
I then filtered this query to only include Selected products (based on the
TABLE_PRODUCT.Selected field, where this field = "-1").
OUTPUT OF QUERY_PRODUCT_AND_LOCATION
In this way, the QUERY_PRODUCT_AND_LOCATION produces a list as such:
idProduct PRODUCT SUPERMARKET
1 Beans Coles
1 Beans Woolworths
1 Beans ALDI
2 Carrots Coles
2 Carrots Woolworths
3 Watermelon (NULL)
4 Chicken Woolworths
4 Chicken ALDI
5 Ham Coles
6 Water (NULL)
7 Olive Oil (NULL)
REPORT_PRODUCT_AND_LOCATION
I want to run a report that lists all products with a corresponding
Supermarket (drawn from an Unbound Combo Box in FORM_PRODUCT_SELECTED). For
the sake of this discussion, I will replace this Combo Box output with
"Coles". After this list, I want the report to list all remaining selected
products in Alphabetical order (which do not have Supermarket = "Coles" and
which have not already been listed in the "Coles" list).
LOGIC OF REPORT
i.e: Report should list the following in order:
Criteria 1. QUERY_PRODUCT_AND_LOCATION: Criteria = "Coles"
Criteria 2. QUERY_PRODUCT_AND_LOCATION: Criteria = <>"Coles" and Is NULL and
<>idProduct where SUPERMARKET = "Coles".
PROBLEM
* I am able to filter my report by Criteria 1 or Criteria 2, but not by
Criteria 1 then Criteria 2.
* Within Criteria 2, I also do not know how I can remove all idProduct that
have been listed in the Criteria 1 filter.
Please help if you know how to resolve this issue. I have a feeling the
resolution will come down to code, but do not have enough talent to determine
the right code.
I am happy to upload my database if this will help you understand the issue
I have. The goal is to achieve watertight logic. Right now my logic has a
couple of leaks..
(potentially linked to separate queries). This report is based on 2 linked
tables. The question is as short as it can be while maintaining the requisite
level of detail. For the bottom line of the summary, scroll to the bottom of
this page.
I created a database with 2 linked tables. The intention is to compare
prices and aisle numbers at different supermarkets, based on a selection of
products.
TABLE_PRODUCT
idProduct - Autonumber
Product
Type
Selected
TABLE_LOCATION_PRICE
idLocation - Autonumber
idProduct - Number (linked to TABLE_PRODUCT)
Supermarket
Brand
Aisle
Price
FORM_PRODUCT_SELECTED
I created a form that provides a list of all available products (from
TABLE_PRODUCT), with check boxes next to them. The output of the check boxes
is stored in TABLE_PRODUCT.Selected. Only a positive selection will record
"-1" in this field.
QUERY_PRODUCT_AND_LOCATION
I linked TABLE_PRODUCT with TABLE_LOCATION_PRICE, in a one-to-many
relationship, where every product is selected, and every supermarket matching
each product is selected. Where there is no Supermarket to match a Product,
the Product is still listed with a NULL entry in the Supermarket field).
I then filtered this query to only include Selected products (based on the
TABLE_PRODUCT.Selected field, where this field = "-1").
OUTPUT OF QUERY_PRODUCT_AND_LOCATION
In this way, the QUERY_PRODUCT_AND_LOCATION produces a list as such:
idProduct PRODUCT SUPERMARKET
1 Beans Coles
1 Beans Woolworths
1 Beans ALDI
2 Carrots Coles
2 Carrots Woolworths
3 Watermelon (NULL)
4 Chicken Woolworths
4 Chicken ALDI
5 Ham Coles
6 Water (NULL)
7 Olive Oil (NULL)
REPORT_PRODUCT_AND_LOCATION
I want to run a report that lists all products with a corresponding
Supermarket (drawn from an Unbound Combo Box in FORM_PRODUCT_SELECTED). For
the sake of this discussion, I will replace this Combo Box output with
"Coles". After this list, I want the report to list all remaining selected
products in Alphabetical order (which do not have Supermarket = "Coles" and
which have not already been listed in the "Coles" list).
LOGIC OF REPORT
i.e: Report should list the following in order:
Criteria 1. QUERY_PRODUCT_AND_LOCATION: Criteria = "Coles"
Criteria 2. QUERY_PRODUCT_AND_LOCATION: Criteria = <>"Coles" and Is NULL and
<>idProduct where SUPERMARKET = "Coles".
PROBLEM
* I am able to filter my report by Criteria 1 or Criteria 2, but not by
Criteria 1 then Criteria 2.
* Within Criteria 2, I also do not know how I can remove all idProduct that
have been listed in the Criteria 1 filter.
Please help if you know how to resolve this issue. I have a feeling the
resolution will come down to code, but do not have enough talent to determine
the right code.
I am happy to upload my database if this will help you understand the issue
I have. The goal is to achieve watertight logic. Right now my logic has a
couple of leaks..