• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

2 Access tables, count verbiage used

I have a table named Orders with field Product. In that field, there is string of text which contains the color of the product. In another table, I have field Color, which has all the possible colors that may be in the text string in the other table. I need to count the number of Products in table Orders that have the Color from the second table. Reason: I need to count how many orders used each possible Color. Ideally, I would add a field called Color in table Orders right after field Product which would store the color found in the other table.
0
QMBB
Asked:
QMBB
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
first, you need to create a UDF to parse the content of the field Product to get all the colors for that record.
next is to compare the result to the values in the  second table.


a sample db is needed to do this.

upload a copy of the db with the two tables.
0
 
QMBBAuthor Commented:
0
 
Rey Obrero (Capricorn1)Commented:
the color field contains text that are not color, is this correct  (21 - 29 in)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QMBBAuthor Commented:
Yes, 2 records, but I need to find those as well.
0
 
Rey Obrero (Capricorn1)Commented:
test this revised db, I added a field color in table orders and populate by running the sub checkColors()

see code In module1  checkColors()
db12_rev.mdb
0
 
QMBBAuthor Commented:
Thanks very much.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now