Iff Query/Form

[Mac]

I have 3 tables/forms.

-Business Unit1
-Business Unit2
-Customer Info

On the customer form i have 3 fields that i would like to relate to eachother.
-Business Unit
-Account
-Manager
I want the Business Unit field to be a drop down to choose either 1 or 2.
This I can do. The next 2 parts I can't do.
The Account field and the Manager fields, I want to be contingent on the
BusUnit field.

ex.
BusUnit = 1
Account = 60102
manager= find manager at intersection of business unit 1 and account
60102..if manager doesn't exist, default display is "null"

Does this make sense? Do you need more information? Thanks.

 

[Jeff Boyce]

Why do you have multiple tables with "repeating" values for the names (e.g.
[Business Unit1], [Business Unit2])? Do both those tables have identical
structures? This is only rarely necessary in a well-normalized relational
database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mac]

These tables do have identical structures. They each contain 5 digit account
numbers, the description of the account, and the manager of that account.
several of the account numbers are the same, depending on the business unit
they fall under, could have a different manager. We have to specify which
business unit we are using, and under that, which account we are pulling
from. I have come to the conclusion that the account number has to be the
primary key, so splitting the business units to 2 diff tables was the
necessary thing to do. If you have an easier solution to this, please let me
know. Ultimately I want to be able to..

-choose a business unit,
-type an acct number (dropdown would take too long, there are over 5,000
accts)
-populate the manager

that quickly.

Does this make sense?

Why do you have multiple tables with "repeating" values for the names (e.g.
[Business Unit1], [Business Unit2])? Do both those tables have identical
structures? This is only rarely necessary in a well-normalized relational
database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 3 tables/forms.

-Business Unit1
-Business Unit2
-Customer Info

On the customer form i have 3 fields that i would like to relate to
eachother.
-Business Unit
-Account
-Manager
I want the Business Unit field to be a drop down to choose either 1 or 2.
This I can do. The next 2 parts I can't do.
The Account field and the Manager fields, I want to be contingent on the
BusUnit field.

ex.
BusUnit = 1
Account = 60102
manager= find manager at intersection of business unit 1 and account
60102..if manager doesn't exist, default display is "null"

Does this make sense? Do you need more information? Thanks.

 

[Jeff Boyce]

Mac

Backup, backup, backup.

Then, before making any of the changes below, backup!

In one table, add [BusinessUnit] as a field.

Update all of that table's records (let's say the table is the BusinessUnit1
table) for that new field to 1.

Append all of the other table's records (BU2) to the revised table, with
BusinessUnit = 2.

Now, when you need to query something to find all BU2 records, query the
single table that holds both BU's records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

These tables do have identical structures. They each contain 5 digit
account
numbers, the description of the account, and the manager of that account.
several of the account numbers are the same, depending on the business
unit
they fall under, could have a different manager. We have to specify which
business unit we are using, and under that, which account we are pulling
from. I have come to the conclusion that the account number has to be the
primary key, so splitting the business units to 2 diff tables was the
necessary thing to do. If you have an easier solution to this, please let
me
know. Ultimately I want to be able to..

-choose a business unit,
-type an acct number (dropdown would take too long, there are over 5,000
accts)
-populate the manager

that quickly.

Does this make sense?

Why do you have multiple tables with "repeating" values for the names
(e.g.
[Business Unit1], [Business Unit2])? Do both those tables have identical
structures? This is only rarely necessary in a well-normalized
relational
database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 3 tables/forms.

-Business Unit1
-Business Unit2
-Customer Info

On the customer form i have 3 fields that i would like to relate to
eachother.
-Business Unit
-Account
-Manager
I want the Business Unit field to be a drop down to choose either 1 or
2.
This I can do. The next 2 parts I can't do.
The Account field and the Manager fields, I want to be contingent on
the
BusUnit field.

ex.
BusUnit = 1
Account = 60102
manager= find manager at intersection of business unit 1 and account
60102..if manager doesn't exist, default display is "null"

Does this make sense? Do you need more information? Thanks.