Searching and calculating fields from a single record

[karmacable]

The way my database is setup is that each record holds all the information I wish to query / calculate / lookup 'only' for that record. I'm currently not interested in querying across records (which is too bad cuz that would certainly make this easier), so that's why I've posted this under the programming thread, figuring querying may not be my best solution.

The basic form setup for each record has about 20 combo boxes (all providing the same choices). So you have 4 groups and each group can choose any combination of 5 of these items. Let me Illustrate;

Group 1___Group 2____Group 3___Group 4


Sony______ Panasonic ___N/A _________N/A
Sony______N/A__________N/A_________N/A
N/A_______Sony_________N/A_________N/A
Nikon______Sony_________Panasonic____N/A
N/A_______Sony_________Nikon________N/A

So the above camera choices show up as combo boxes (looking up from another table) on the form. They save the choices by their lookup ID in the current form's corresponding fields. Also, the fields are 'not' relational between both tables, which is why querying won't work. I've done it this way for a very specific reason (discovering through much trial and error), so please don't worry about giving me advice on joining the tables and not needing to save the choices to fields.

I now have a bunch of fields in a single record which I need to do a search across to make summations for an executive report. So for the above project, the end result would be;

Group 1 has 2 x Sony & 1 Nikon
Group 2 has 2 x Sony & 1 Panasonic
Group 3 has 1 x Panasonic & 1 Nikon

All Groups 4 x Sony & 2 Panasonic & 2 Nikon

I apologize if this thread should have been started in the query / report section, but I'm not seeing how to do such activities with Access regular functions, so I'm thinking this will probably be some programming and the generation of a pop-up form as the eventual report.

Thanks to anyone pointing me in the right direction

[June7]

Minor typo in your data sample. Group2 has 3 Sony and 1 Panasonic.

Here is possible query approach. First, do 4 Totals queries, one for each group, then UNION the four queries.

The Totals queries would be like:
SELECT Group1, Count(Group1) AS CountOfGroup1 FROM Table1 GROUP BY Group1;

The Union would then be:
SELECT "Group1" As GroupSource, * FROM Group1Count
UNION SELECT "Group2", * FROM Group2Count
UNION SELECT "Group3", * FROM Group3Count
UNION SELECT "Group4", * FROM Group4Count;

There is no designer or wizard for UNION query, must type in the SQL View editor of the query designer.

Now use the UNION query as RecordSource for a report.

Could be a single nested query by replacing the Totals queries names in the UNION with the Totals SQL statements. Be sure to enclose in parentheses.

[karmacable]

Let's say we have 12 combo boxes on a form (I actually have 40, but for this example let's keep it small). Those combo boxes are broken into 4 groups of 3. I'm trying to figure out the VBA code that would in essence create a range out of each group. This would be so that later I could make loop statements to check out / compare the values of each combobox against it's own group.


I am currently having to write verrrrry long If / Then statements like;

 
If cbo1 = cbo2 And cbo2 = cbo3 Then
   (answer)
ElseIf cbo1 <> cbo2 And cbo2 = cbo3 Then
   (answer)
ElseIf........

Well, you should hopefully get the picture of all the combinations to test through, and the above is just for one group!

Once someone has helped with that conundrum (or I happen to figure it out), the next logical question from me would be how to setup the testing statement? In other words, how do I have cbo1 check it's value against the other two cbo's, and so forth?

Thanks for any pointers in the right direction.

[June7]

Not understanding your requirements. Why would two combos have the same value? What is purpose of this data entry?

[karmacable]

Let me illustrate an example (all the company names represent combo boxes);

Group 1___Group 2____Group 3___Group 4
Sony______ Panasonic ___N/A _________N/A
Sony______N/A__________N/A_________N/A
N/A_______Sony_________N/A_________N/A
Nikon______Sony_________Panasonic____N/A
N/A_______Sony_________Nikon________N/A

I now have a bunch of combo boxes on a form, in a single record, which I need to do a search across to make summations for an executive report. So for the above project, the end result would be;

Group 1 has 2 x Sony & 1 Nikon
Group 2 has 3 x Sony & 1 Panasonic
Group 3 has 1 x Panasonic & 1 Nikon

All Groups 5 x Sony & 2 Panasonic & 2 Nikon

So regardless of my requirements, 'how' can I accomplish what I initially asked? Thanks to anyone pointing me in the right direction

[June7]

Note for new reader: duplicate threads combined by moderator.

[karmacable]

Not sure why the moderator combined this thread with my older thread (that was a question on querying which didn't work out)?

In this thread I'm using my old example (copied and pasted to save time) to try and solve a completely different question regarding VBA coding, not querying.

[June7]

You may ask moderator to split the threads back as they were. Click the triangle at upper left of post.

Posting the 'old example' in total was misleading. Made me think you were still trying to achieve the same goal, especially since you never responded about my suggestion in the first thread. Do you still need to resolve that question? Why didn't it work? I built tables and tested the queries and they worked great.

As for the programming question, I see no alternative. Perhaps if I better understood what you were trying to accomplish, what is the reason for all this comparing?

[karmacable]

Sorry for not responding to your original post. The problem I discovered with your method is that I would need to create multiple copies of the same table to have them uniquely linked to each combo box. If there were only a few comboboxes, that's no problem. However, I have tons of them and it would get pretty massive.

For a full understading of what that issue is, see this thread where someone attempted to help me and finally understood what I was talking about;
https://www.accessforums.net/queries...ble-17293.html

In any case, If you'd be willing to post up your db example where you tried out the union queries, I'd be interested in learning from it.

Thanks,

[June7]

The original thread and question concerned output for a report. Not applicable to data entry form. As a solution for a report I think my explanation is clear enough for you to reproduce. I have encountered a limit of 50 lines for a UNION query. As ssanfu noted, your data is not normalized and you will continue to hit walls like this if you stick with this structure.

If you saved the key from the lookup in each field, this would mean 50 joins to the lookup table in the SELECT query to retrieve the related info. Yes, 50 joins in a SELECT would be a long query. The most I seem to have is 30. Think that is because I hit limit for number of fields in a query. Had to use a subreport to get everything pulled onto the report. But if you don't have or care about other related data, then saving the actual names is just fine.

I still don't understand why code comparing comboboxes.