Multi column Combobox

  • Thread starter Thread starter Charles G via AccessMonster.com
  • Start date Start date
C

Charles G via AccessMonster.com

Tables:
Account Analysis Input Table (location for all data; 11 fields)
AA Client List Table (contains 2 fields: Client Name, Client Account #)

I've created a Form with a 2-column combo box containing Client Name and
Client Account #. This form will shoot the information into my Account
Analysis Input Table. However, my combo box only inputs data into one of the
two fields in my Account Analysis Table. I want my combo box containing the
Client Name and Client Account # to populate the Client Name field and Client
Account # field in my Account Analysis Table. Any suggestions.

For what it's worth, I will be creating reports from a query. However, only
one Table will hold all the vital information.

Probably a simple question for the Access guru's, sorry for such a Newb
question.

-Charles-
 
Charles G via AccessMonster.com said:
Tables:
Account Analysis Input Table (location for all data; 11 fields)
AA Client List Table (contains 2 fields: Client Name, Client Account
#)

I've created a Form with a 2-column combo box containing Client Name
and Client Account #. This form will shoot the information into my
Account Analysis Input Table. However, my combo box only inputs data
into one of the two fields in my Account Analysis Table. I want my
combo box containing the Client Name and Client Account # to populate
the Client Name field and Client Account # field in my Account
Analysis Table. Any suggestions.

For what it's worth, I will be creating reports from a query.
However, only one Table will hold all the vital information.

Probably a simple question for the Access guru's, sorry for such a
Newb question.
Click to expand...

Why do you want to put both the client name and the client account# into
the Account Analysis Input Table? Isn't one of these two fields the
primary key of [AA Client List Table]? It's normally a bad idea to put
both the key to lookup table and the lookup table itself into another
table -- if you do, you are not only duplicating data (which is
something that relational databases are designed to prevent), but also
running the risk that at some point the related data will change, and so
you will have the same account# associated with different client names
in different table (or else the other way around, if [Client Name] is
the primary key of [AA Client List Table]. This would be an
unfortunate circumstance.

Yes, it is possible to do what you are asking, and store both fields
from the combo box's rowsource into the Account Analysis Input Table.
But it strikes me as such a bad idea that I hope I can talk you out of
it. Better would be to store only the client's primary key -- whether
account# or name -- and use a query that joins the two tables on that
key whenever you need the other info from the [AA Client List Table].
If you need it often, you can create a query that does the join and
selects all the information from both tables, and base all reports or
other queries on that one.
 
Tables:
1.) AA Client List Table
Fields (2):
A. Client Name (primary key)
B. Client Account Number

2. Account Analysis Input Table
Fields (12):
A. Client Name (primary key)
B. Client Account #
C. Service Code
D. etc. etc. etc.

Queries:
1. Account Analysis Query
Fields (12):
A. Same as Account Analysis Input Table

The query joins both the AA Client List Table and Account Analysis Table.
I've selected option 2 where it states "Select ALL records from the Account
Analysis Input Table and only those records from the AA Client List Table
where the joined fields are equal."

Reports will be pulled from the "Account Analysis Query" based on the Month
of Activity. I will need these fields to show on the report:

1. Client Name
2. Client Account #
3. Service Code
4. Volume
5. Month of Activity

Is this a safer way to store the data? Now, how can I get that both the
Client Name and Client Account Number to appear on my report? After keying
the data in my Form, wouldn't the Client Name and Account # show up in the
table that my Form is built from?

-Charles-
 
Charles G via AccessMonster.com said:
Tables:
1.) AA Client List Table
Fields (2):
A. Client Name (primary key)
B. Client Account Number

2. Account Analysis Input Table
Fields (12):
A. Client Name (primary key)
B. Client Account #
C. Service Code
D. etc. etc. etc.

Queries:
1. Account Analysis Query
Fields (12):
A. Same as Account Analysis Input Table

The query joins both the AA Client List Table and Account Analysis
Table. I've selected option 2 where it states "Select ALL records
from the Account Analysis Input Table and only those records from the
AA Client List Table where the joined fields are equal."

Reports will be pulled from the "Account Analysis Query" based on the
Month of Activity. I will need these fields to show on the report:

1. Client Name
2. Client Account #
3. Service Code
4. Volume
5. Month of Activity

Is this a safer way to store the data? Now, how can I get that both
the Client Name and Client Account Number to appear on my report?
After keying the data in my Form, wouldn't the Client Name and
Account # show up in the table that my Form is built from?
Click to expand...

There's a basic design flaw here, at least as you describe the tables to
me. You say:
1.) AA Client List Table [..]
A. Client Name (primary key) [...]
2. Account Analysis Input Table [...]
A. Client Name (primary key)
Click to expand...

Does the Account Analysis Input Table really have only one record per
client, as is implied by its having [Client Name] as its primary key?
If so, there's no reason for [AA Client List Table] to exist. What sort
of information goes into [Account Analysis Input Table]? It looks as
though [Account Analysis Input Table] ought to contain multiple records
per client; maybe one record per service code per client, or something
like that. If that's so, then [Client Name] *cannot* be the primary key
of that table, though it could combine with another field to make a
compount primary key.

Is [Client Account Number] unique for each client? Could there be two
clients with the same account number? Could there be two account
numbers for the same client?
 
Does the Account Analysis Input Table really have only one record per
client, as is implied by its having [Client Name] as its primary key?
If so, there's no reason for [AA Client List Table] to exist. What sort
of information goes into [Account Analysis Input Table]?
Click to expand...

The Account Analysis Table will hold mutiple records for each of the 32
Clients. *Some of the Clients share Account #'s.* I see how that eliminates
the Primary Key for those two fields. Of the 32 Client's, 31 have individual
Account #'s with, for example, Belk #1 and Belk #2 sharing an Account #. I
will be entering the data for all 32 Clients.

The AA Client List Table exists so I could create a combo box with a drop
down arrow so I can select the 32 clients from that list. Is there a better
way to create a list of clients in a form to pick from?

I need to duplicate data becaues each client will be making entries into this
table and it's possible that every field could contain duplicate data. With
that being said, I don't have a Primary Key. Is this a bad thing?

-Charles-
 
Charles G via AccessMonster.com said:
Does the Account Analysis Input Table really have only one record per
client, as is implied by its having [Client Name] as its primary key?
If so, there's no reason for [AA Client List Table] to exist. What
sort of information goes into [Account Analysis Input Table]?
Click to expand...

The Account Analysis Table will hold mutiple records for each of the
32 Clients. *Some of the Clients share Account #'s.* I see how that
eliminates the Primary Key for those two fields. Of the 32 Client's,
31 have individual Account #'s with, for example, Belk #1 and Belk #2
sharing an Account #. I will be entering the data for all 32 Clients.
Click to expand...

So each client has only one account#, but one account# might be used by
more than one client. That's perfectly reasonable. So the structure of
your table AA Client List Table] is correct, but your [Account Analysis
Input Table] is not. I'll get to that in a minute.
The AA Client List Table exists so I could create a combo box with a
drop down arrow so I can select the 32 clients from that list. Is
there a better way to create a list of clients in a form to pick from?
Click to expand...

No, that's fine the way it is.
I need to duplicate data becaues each client will be making entries
into this table and it's possible that every field could contain
duplicate data. With that being said, I don't have a Primary Key. Is
this a bad thing?
Click to expand...

As a rule, it's best to give every table a primary key, so that the
database engine -- any database engine -- can always identify the
specific record that needs to be acted upon (selected, updated,
deleted). Access doesn't force you to do that, at least not for tables
stored in an .mdb file, but it's still a good idea. If you could
logically have records in your [Account Analysis Input Table] that are
duplicates in every field, I'd suggest adding an autonumber field to the
table and setting it as the primary key. That way, every record will be
assigned a unique, system-generated value that can be used to identify
it. You and your application don't care what this value is, and you
don't need to care; it's enough that the database engine knows about
it.

Further, you should remove the [Client Account #] field from that table.
This field is specific to the client, and is not dependent on any other
field in [Account Analysis Input Table] except the [Client Name]
field -- it's always the same as the value stored in the [Client Account
Number] field of the record in [AA Client List Table] that has the same
[Client Name]. Instead of copying this field into the [Account Analysis
Input Table], base all forms and reports that need the account number on
a query that joins the two tables. Such a query might have SQL like
this:

SELECT
[Account Analysis Input Table].*,
[AA Client List Table].[Client Account Number]
FROM
[Account Analysis Input Table]
INNER JOIN [AA Client List Table]
ON [Account Analysis Input Table].[Client Name] =
[AA Client List Table].[Client Name];
 
Further, you should remove the [Client Account #] field from that table.
This field is specific to the client, and is not dependent on any other
field in [Account Analysis Input Table] except the [Client Name]
field -- it's always the same as the value stored in the [Client Account
Number] field of the record in [AA Client List Table] that has the same
[Client Name]. Instead of copying this field into the [Account Analysis
Input Table], base all forms and reports that need the account number on
a query that joins the two tables. Such a query might have SQL like
this:

SELECT
[Account Analysis Input Table].*,
[AA Client List Table].[Client Account Number]
FROM
[Account Analysis Input Table]
INNER JOIN [AA Client List Table]
ON [Account Analysis Input Table].[Client Name] =
[AA Client List Table].[Client Name];
Click to expand...

The table is at work and I can't access it at home, but I'll give it a shot
on Monday. Thanks for your patience and guidance.

-Charles-
 
I've changed my DB as follows:

Tables:
1. Account Analysis Input Table
Primary Key is the Autonumber in the 1st column. Next, [Client Name],
[Service Code], [Volume], [Pennies], [Nickels], [Dimes], [Quarters], [Halves],
[Boxed Coin], C[urrency], [Month of Activity]. I've deleted [Client Account #]
as a field in this table.

2. AA Client List Table
Primary Key [Client Name]. 2nd column is [Client Account #]

Query
I will create reports from this query for the fields:
[Client Name], [Client Account #], [Service Code], [Volume], and [Month of
Activity] which will also serve as a parameter.

Form
 
I've changed my DB as follows:

Tables:
1. Account Analysis Input Table
Primary Key is the Autonumber in the 1st column. Next, [Client Name],
[Service Code], [Volume], [Pennies], [Nickels], [Dimes], [Quarters], [Halves],

[Boxed Coin], [Currency], [Month of Activity]. I've deleted [Client Account #]

as a field in this table.

2. AA Client List Table
Primary Key [Client Name]. 2nd column is [Client Account #]

Query
I will create reports from this query for the fields:
[Client Name], [Client Account #], [Service Code], [Volume], and [Month of
Activity] which will also serve as a parameter.

Relationships
Table/Query Related Table/Query
Client Account # Client Name
Client Name Client Name

Join Properties
Option 3: Include ALL records from 'Account Analysis Input Table"'and only
those from 'AA Client List Table' where the joined fields are equal.
Relationship Type: One-To-Many

Form
Here is where I run into my problem, at least I think.
1. Account Analysis Input Form
My first control is a 2-column combo box with [Client Name] and [Client
Account #] for 32 clients. Maybe I have a join incorrect or am missing a
relationship, but here is what I have:

Name: Client Name
Control Source: Client Name
Row Source Type: Table/Query
Row Source: AA Client List Table
Column Count: 2
Column Heads: Yes
Column Widths:
Column Bound: 1

When I run my query, everything works except that it doesn't input data into
the [Client Account #] field.

-Charles-
 
Charles G via AccessMonster.com said:
I've changed my DB as follows:

Tables:
1. Account Analysis Input Table
Primary Key is the Autonumber in the 1st column. Next, [Client
Name], [Service Code], [Volume], [Pennies], [Nickels], [Dimes],
[Quarters], [Halves],

[Boxed Coin], [Currency], [Month of Activity]. I've deleted [Client
Account #]

as a field in this table.

2. AA Client List Table
Primary Key [Client Name]. 2nd column is [Client Account #]
Click to expand...

So far, so good.
Query
I will create reports from this query for the fields:
[Client Name], [Client Account #], [Service Code], [Volume], and
[Month of Activity] which will also serve as a parameter.

Relationships
Table/Query Related Table/Query
Client Account # Client Name
Client Name Client Name

Join Properties
Option 3: Include ALL records from 'Account Analysis Input Table"'and
only those from 'AA Client List Table' where the joined fields are
equal. Relationship Type: One-To-Many
Click to expand...

This part of your description isn't clear to me. Generally, it's best
to post the SQL view of the query. Your query should show only one join
line between the tables, connecting the [Client Name] fields in each
table. The query's field list (in the field grid) should not include
two copies of the [Client Name] field, one from each table -- it should
have only the [Client Name] from the [Account Analysis Input Table].
Only the [Client Account #] field should be included from the [AA Client
List Table].

Fix the query, if necessary, and if the form still doesn't work, post
the SQL view of the query.
Form
Here is where I run into my problem, at least I think.
1. Account Analysis Input Form
My first control is a 2-column combo box with [Client Name] and
[Client Account #] for 32 clients. Maybe I have a join incorrect or
am missing a relationship, but here is what I have:

Name: Client Name
Control Source: Client Name
Row Source Type: Table/Query
Row Source: AA Client List Table
Column Count: 2
Column Heads: Yes
Column Widths:
Column Bound: 1

When I run my query, everything works except that it doesn't input
data into the [Client Account #] field.
Click to expand...

What is the RecordSource of the form? It should be the query you
described above, or a SQL statement that selects fields from that
query -- not the ['Account Analysis Input Table]. Make sure the [Client
Account #] text box on the form is bound to the [Client Account #] field
in the query.
 
Thank you so very much for your patience. It WORKS!!! I'm such an Access
newbie, however, I would have never gotten this without your help. Three
weeks of work on this simple step has paid off. Thanks again.

-Charles-
 
Charles G via AccessMonster.com said:
Thank you so very much for your patience. It WORKS!!!
Great!

Three weeks of work on this simple step has paid off. Thanks
again.
Click to expand...

But now you know a lot more than you did before, so this sort of thing
will be easier in the future. You're welcome.
 
Back
Top