Access subquery question

  • Thread starter Thread starter just a guy
  • Start date Start date
J

just a guy

I need help in writing subqueries for selection of valid entries, for
the ROW SOURCE for an Access table item. I have tried to keep the
explanation as succinct as possible. My actual questions are
contained in the explanation items b and c at the end of this posting.
In advance, thanks for all suggestions, code samples, URLs, etc.

Disclaimer: I'm not an Access developer or SQL guru, , but I did RTF
OLHF (online help files). I'm a power user who has done lots of
programming in the distant past, and I have gotten part of the way to
a solution.

My project is a tracking system for my software (including lots of
shareware), and software installs on the different systems in my home
LAN.

I have defined these tables:
1. Vendors - for naming consistency
2. Categories - for naming consistency

3. Software Products Database - includes vendor name (combo box using
Vendors table), category (combo box with Categories table), product
name, release level, product serial number, and miscellaneous
information. Got the Combo Boxes to work exactly as I wanted, using
DISTINCTROW and sorting ascending. I'm pleased that auto-completion
seems built-in to Access.

4. Software install action types - new install, patch, upgrade in
place, removal.
5. Software Installs table [actually one for each system] - includes
vendor name, product category, release level, software install action
types, plus dates and install notes.

For Table 5, I want to use combo boxes for vendor name, product
category, product name and release level, based on the equivalent
filelds in the Software Products Database, to enforce consistency
between the Software Products Database and the Software Installs
tables.

To illustrate what I'm trying to do,
a. For the vendor field, the Combo Box for vendors can contain the
names of all the vendors in the Software Products Database.

b. Once I have selected a vendor, the category field Combo Box should
display only those catogories that match up with that vendor. E.g. if
the vendor is Microsoft, the category could be "operating system,"
"productivity," or "game", but not "utility" or "Linux." For vendor
Adobe, the only allowable category is "productivity."

c. Once I have selected a vendor and category, the Combo Box for
product name should contain only those product names from the Software
Products Database table that match up with the vendor and category.

When I can accomplish b and c, I think I'm done. Again, thank you for
reading this.
 
-----Original Message-----
I need help in writing subqueries for selection of valid entries, for
the ROW SOURCE for an Access table item. I have tried to keep the
explanation as succinct as possible. My actual questions are
contained in the explanation items b and c at the end of this posting.
In advance, thanks for all suggestions, code samples, URLs, etc.

Disclaimer: I'm not an Access developer or SQL guru, , but I did RTF
OLHF (online help files). I'm a power user who has done lots of
programming in the distant past, and I have gotten part of the way to
a solution.

My project is a tracking system for my software (including lots of
shareware), and software installs on the different systems in my home
LAN.

I have defined these tables:
1. Vendors - for naming consistency
2. Categories - for naming consistency

3. Software Products Database - includes vendor name (combo box using
Vendors table), category (combo box with Categories table), product
name, release level, product serial number, and miscellaneous
information. Got the Combo Boxes to work exactly as I wanted, using
DISTINCTROW and sorting ascending. I'm pleased that auto- completion
seems built-in to Access.

4. Software install action types - new install, patch, upgrade in
place, removal.
5. Software Installs table [actually one for each system] - includes
vendor name, product category, release level, software install action
types, plus dates and install notes.

For Table 5, I want to use combo boxes for vendor name, product
category, product name and release level, based on the equivalent
filelds in the Software Products Database, to enforce consistency
between the Software Products Database and the Software Installs
tables.

To illustrate what I'm trying to do,
a. For the vendor field, the Combo Box for vendors can contain the
names of all the vendors in the Software Products Database.

b. Once I have selected a vendor, the category field Combo Box should
display only those catogories that match up with that vendor. E.g. if
the vendor is Microsoft, the category could be "operating system,"
"productivity," or "game", but not "utility" or "Linux." For vendor
Adobe, the only allowable category is "productivity."

c. Once I have selected a vendor and category, the Combo Box for
product name should contain only those product names from the Software
Products Database table that match up with the vendor and category.

When I can accomplish b and c, I think I'm done. Again, thank you for
reading this.

.
There would be no way of accomplishing this the way you
Click to expand...
describe. Your products table doesn't appear to be
related to vendors in any way I can see. It may be
related to categories, but categories are not related to
both products and vendors.

In your example, the three tables describe are not related
in any common way. You need a way to relate Microsoft to
Product X, and the fact that product X is in a certain
category does nothing to indicate whether Microsoft
handles Product X. You will need another table to link
Microsoft to Product X, not just the category that product
X is in. Best of luck!
 
Back
Top