Too Many Union Queries

[AmyDiLo]

HELP!!! I have a created a database to monitor all of our account
investment guidelines and test for compliance. I have 2 main tables.
One contains a list of 118 accounts (the acct # is the primary key)
and 65 guidelines. Some of these guidelines are specific to only a
few accounts, while others apply to all. The other table has been
built using Update queries to populate all the values of the
constraints (again the account # is the primary key). I was building
union queries off of these tables, so I could have a report that would
look up all the guidelines for a specific account with the constraint
and current values. I was also going to build a Combo Box to generate
reports for an individual constraint across the accounts. I have
maxed out the number of union queries that I can have at 22 and was
wondering if anyone had any ideas on how to remedy the situation. I
really do not have a background in VBA or SQL. I am a "self taught"
Access user (scary, I know). If anyone has any suggestions, I would
really appreciate it. Please be kind

Thanks,

Amy

 

[Guest]

You need to normalize your data. I'm assuming that the 65 guidelines are
across the table in fields something like:

GuideLine1 GuideLine2 GuideLine3 GuideLine4 GuideLine5
yes no no no yes
no n/a no yes

If so, the problem with union queries is just one of many problems that you
will hit. I highly recommend getting some relational database training or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further on this database.

 

[John W. Vinson]

HELP!!! I have a created a database to monitor all of our account
investment guidelines and test for compliance. I have 2 main tables.
One contains a list of 118 accounts (the acct # is the primary key)
and 65 guidelines. Some of these guidelines are specific to only a
few accounts, while others apply to all.

Well, that's *three* tables right there:

Accounts
AccountNo <don't use # in fieldnames, it's a date delimiter>
<information specific to the account>

Guidelines
GuidelineID
Guideline <Text>

Compliance
AccountNo
GuidelineID

The other table has been
built using Update queries to populate all the values of the
constraints (again the account # is the primary key). I was building
union queries off of these tables, so I could have a report that would
look up all the guidelines for a specific account with the constraint
and current values.

Much easier with the normalized design.

I was also going to build a Combo Box to generate
reports for an individual constraint across the accounts. I have
maxed out the number of union queries that I can have at 22 and was
wondering if anyone had any ideas on how to remedy the situation. I
really do not have a background in VBA or SQL. I am a "self taught"
Access user (scary, I know). If anyone has any suggestions, I would
really appreciate it. Please be kind

Normalize... you'll be glad you did. <g>

John W. Vinson [MVP]