8 table need to design queries for all possible combinations...

[hardy1976]

8 tables; need to design queries for all possible combinations...

Hi,

HELP!

I have 8 tables all with a membership number as the unique identifier, I need to find all the possible combinations and then create queries for each combination, is there a quick and easy way to do this? or do I have to sit and define a query for each of the 250ish combinations?!

I'm pulling all the data together using a union query. What I need to do now is to find the combinations using either "is null" or "is not null" criteria (in the queries).

Regards
H.

 

[neileg]

Bit short of information here. I'm suspecting you may have a design problem if you are using a union query.

The answer to your query problem may well involve building the query SQL in text.

 

[hardy1976]

I have 8 seperate files, which I have imported into Access - 8 tables. All some or none of the files could have the same membership number. What I need to do is to find totals for each potential combination for example

file 1
membership number = 123
amount = £10
membership number = 456
amount = £50

file 2
membership number = 123
amount = £50
membership number = 789
amount = £100

I would need a report that showed combinations of one files only, both files. so in this case its quite easy as there are so few combinations, however doing this with 8 files increase the number of combinations you could have to over 250!

HTH and makes more sense.
H.

 

[The_Doc_Man]

Your dataset is terribly not normalized. You have 1 table. And one table only.

Before you take a step further, google-search "database normalization" and review the articles from .EDU sites where you recognize and respect the college or university in question. Also, vendor articles where you recognize and respect the vendor. Also the MS Access Help files have a brief discussion on the topic.

You need to keep this normalization principle in mind when designing your table.

For access, if you can get your data to 3rd normal form, that is usually enough to make the data usable without TOO many artifacts brought on by poor data layout.

 

[neileg]

I have 8 seperate files, which I have imported into Access - 8 tables. All some or none of the files could have the same membership number. What I need to do is to find totals for each potential combination for example

file 1
membership number = 123
amount = £10
membership number = 456
amount = £50

file 2
membership number = 123
amount = £50
membership number = 789
amount = £100

I would need a report that showed combinations of one files only, both files. so in this case its quite easy as there are so few combinations, however doing this with 8 files increase the number of combinations you could have to over 250!

HTH and makes more sense.
H.

Nope. I keep reading this but I'm still lost in the static!

I don't see 250 combinations selecting two files from 8, so I'm not understanding the task you have. Are you saying that you want to extract every possible permutation of up to 8 entries for every membership number? That looks like 28 combinations to me.

To be honest, even then I don't know how you would do this without writing 28 queries.

 

[hardy1976]

Nope. I keep reading this but I'm still lost in the static!

I don't see 250 combinations selecting two files from 8, so I'm not understanding the task you have. Are you saying that you want to extract every possible permutation of up to 8 entries for every membership number? That looks like 28 combinations to me.

To be honest, even then I don't know how you would do this without writing 28 queries.

I indeed using Access to "obtain" a report, this is not a "REAL" database!

Sorry I was not clear, the possible combinations are; 1 of the 8 files, 2 of the 8, 3 of the 8, 4 of the 8, 5 of the 8, 6 of the 8, 7 of the 8 and all eight files, with all the possible combinations within each group! NIGHTMARE!

 

[neileg]

Mmm....

Still only 36 combinations.
8 ways of getting 1 file, 7 for 2, 6 for three, ..., 1 way of getting 8 files = 36

But I don't have an answer to the question, anyway!

 

[hardy1976]

Mmm....

Still only 36 combinations.
8 ways of getting 1 file, 7 for 2, 6 for three, ..., 1 way of getting 8 files = 36

But I don't have an answer to the question, anyway!

1/8 8
2/8 28
3/8 56
4/8 70
5/8 56
6/8 28
7/8 8
8/8 1
= 255 combinations!


eg - 2/8 as a start, you can have 1/2, 2/3, 3/4, 4/5, 5/6, 7/8,8/1,2/4,2/5, 2/6, 2/7, 2/8, 3/1, 3/5, 3/6, 3/7, 3/8 etc etc etc etc....

ty for you efforts anyway! ITS A MARE!

 

[neileg]

Duh! Senior moment...

 

[gemma-the-husky]

i can see where op gets 256 from neil (or 255 exclusing select none)

8 files, and you can select each file, or not , as you require - therefore 2^8 = 256.

 

[hardy1976]

i can see where op gets 256 from neil (or 255 exclusing select none)

8 files, and you can select each file, or not , as you require - therefore 2^8 = 256.

But is there a way of creating the queries without creating the queries if you know what I mean!

I there some magical dynamic way of doing them? even in VBA?

 

[The_Doc_Man]

This is not intented to be harsh, but we don't have time to waste on excessive politeness. Please take this as a direct statement and not as an attempt to be abusive.

The problem with magic is that it doesn't exist. It is always an illusion. Right now YOU have an illusion in front of you. There are NOT EIGHT TABLES. I don't care WHAT you think you see. At most there are 8 segments of ONE LARGER TABLE.

You have locked your imagination out of this problem - which in Access, as in most programming problems, is fatal. Your description falls far short of telling us what you REALLY want. The descriptive is just that - a superficial descriptive statement. What is the business PURPOSE of this situation?

We tend to be fairly good at either finding solutions or finding workarounds. But to do that, we need something to go on, to establish perspective, to give us an insight as to your goal. Right now, though... clear as mud.

 

[hardy1976]

This is not intented to be harsh, but we don't have time to waste on excessive politeness. Please take this as a direct statement and not as an attempt to be abusive.

The problem with magic is that it doesn't exist. It is always an illusion. Right now YOU have an illusion in front of you. There are NOT EIGHT TABLES. I don't care WHAT you think you see. At most there are 8 segments of ONE LARGER TABLE.

You have locked your imagination out of this problem - which in Access, as in most programming problems, is fatal. Your description falls far short of telling us what you REALLY want. The descriptive is just that - a superficial descriptive statement. What is the business PURPOSE of this situation?

We tend to be fairly good at either finding solutions or finding workarounds. But to do that, we need something to go on, to establish perspective, to give us an insight as to your goal. Right now, though... clear as mud.

I have 8 print files, for which i need totals, the totals I require are first a count on the the number of people in each group and secondly a sum per total (monetary value).

A person can be in 1,2,3,4,5,6,7 0r all 8 of the files... I need a means of pulling all the data together and then find the totals for each possible combination.

Pulling the data together IS NOT A PROBLEM its finding a quicker way of setting up the queries rather than CREATING 256 DIFFERENT QUERIES.

 

[hardy1976]

If I have a table with all the possible combinations... can I write something that will look through the table and use the data entered in the table as the criteria for a query / report and then run of one report?

 

[neileg]

You still haven't explained the business process. Why do you want to do this.

 

[Michael J Ross]

Is what you are trying to do is to calculate a total for each membership no in each of the eight files, and have that presented in a single query?

If so put all your 8 tables into a single table and create a totals query that groups on file no and membership no and sums on amount.

 

[hardy1976]

You still haven't explained the business process. Why do you want to do this.

We need control totals to reconcile to! The totals much agree prior to pressing ahead with printing.

 

[chergh]

Go back and change the way you store this info it's likely to be quicker than writing 255 queries.

 

[stopher]

We need control totals to reconcile to! The totals much agree prior to pressing ahead with printing.

I agree with the other contributors. The idea that there are 200+ combinations is a complete red herring not helped by the fact that it’s not entirely clear what you want to do.

Perhaps if we assume there are only two tables for the moment. Perhaps you could then describe what it is you want to sum and compare between the tables.


Here’s my take on it… You want to sum each table by MembershipNo. Then you want to compare this total with the same from the next table?

Here’s my approach…
Put all the data in one table with a new field to identify the table number e.g. tbID. Or create a union query that will give you a “virtual” single table again adding the tbID field.
Then create a cross-table query based on the above source with the Membership number as a Row header and the tbIB as the Col header.
The last bit is to find rows (members) where all the columns are not the same. The trick here I think is to create a new query that checks that each of the 8 columns equals the average of the 8 columns. Ok, this might be a long statement with 8 clauses but it’s better than 200+ queries

The reason for going the route of a cross-tab rather than a strainght join is that you will have problems ensuring that all Members are included in the join.

One final point. I don't think you can base a cross-tab query on a union query so you will have to use a make-table to create a temp table.

Hth
Stopher

 

[hardy1976]

FINAL ATTEMPT.. Sorry I'm not expliaing this very well

I have 8 print files all have the identifier of mem number.

Within each file I have a monetary amount.

I need to create a single row pulling all information from the 8 files together, using the mem number, bearing in mind he can be in 1 or all of the files (currently using a union query and then grouping by mem number and summing the monetary values).

Then I need totals at group (or combination) level and an item count of the number of members in each group. For example - how many people have the combination of file 1,2 and 3 AND what was the total for that group, total as in the total for file 1 in that group, 2 for that group and 3 for that group.

Hope that makes more sense really do... H.