Combination of table information

[Rudi]

Hi. I'm a noobie in the MS Access world and I need a little help.

I have 2 tables that look something like this:

Customer
- ID_customer (Autonumber)
- Name
- ID_respresentative

Representative
- ID_representative
- Name_representative

The relation between them is like this: a customer can have none, one
or more representatives. A representative can represent only one
customer at a time.

How can I create a table that would show all the customers (the
customers that have representatives and the ones that don't) and their
representatives information.

example:

ID_cusomer | Name | ID_representative | Name_representative

1 | Customer1 | 1 | Representative1
1 | Customer1 | 2 | Representative2
2 | Customer2 | |
3 | Customer3 | 3 | Representative3
4 | Customer4 | |

Any help would be greatly appreciated.

 

[Allen Browne]

Rudi, do you need to track:
a) the different customers a representative handled over time, or
b) just the current customer for each rep?

If (b), just add an extra field to your Representative table:
- ID_customer
On the form, add a combo box so you can choose the Customer for the rep. Job
done.

If (a), you need an extra table:
CustomerRepresentative table:
- ID_customer (Number)
- ID_representative (Number)
- StartDate Date/Time
Then create a subform in your Representative form, bound to this table.
Each record in the subform represents the customer that the rep handled. The
most recent date is the current one.

 

[Allen Browne]

Rudi, do you need to track:
a) the different customers a representative handled over time, or
b) just the current customer for each rep?

If (b), just add an extra field to your Representative table:
- ID_customer
On the form, add a combo box so you can choose the Customer for the rep. Job
done.

If (a), you need an extra table:
CustomerRepresentative table:
- ID_customer (Number)
- ID_representative (Number)
- StartDate Date/Time
Then create a subform in your Representative form, bound to this table.
Each record in the subform represents the customer that the rep handled. The
most recent date is the current one.

 

[Rudi]

Hi.

Thank you for responding!

I need to track the customers and their representative(s) (if any -
some customers don't have a representative). This way I will be able to
pick a certain customer with a certain representative via a combo box
on my order form.

I can't use the suggested solutions.

In the (a) suggestion the new CustomerRepresentative table will track
only customers that have a representative. But I also have to track the
customers that don't have a representative.

The (b) solution only tracks none or one representative for a certain
customer. But I also have customers with multiple representatives.

If you think of any other solution PLEASE let me know. Becouse I'm
starting to lose my hope about this.

 

[Rudi]

Hi.

Thank you for responding!

I need to track the customers and their representative(s) (if any -
some customers don't have a representative). This way I will be able to
pick a certain customer with a certain representative via a combo box
on my order form.

I can't use the suggested solutions.

In the (a) suggestion the new CustomerRepresentative table will track
only customers that have a representative. But I also have to track the
customers that don't have a representative.

The (b) solution only tracks none or one representative for a certain
customer. But I also have customers with multiple representatives.

If you think of any other solution PLEASE let me know. Becouse I'm
starting to lose my hope about this.

 

[Rudi]

@Allen Browne

P.S. - I checked out your Access webpage
(http://allenbrowne.com/tips.html). Great stuff. I will certainly look
it over when I'll have a little extra time.

 

[Rudi]

@Allen Browne

P.S. - I checked out your Access webpage
(http://allenbrowne.com/tips.html). Great stuff. I will certainly look
it over when I'll have a little extra time.

 

[Rudi]

Correction. The (b) suggestion doesn't include the customers that don't
have a representative.

 

[Rudi]

Correction. The (b) suggestion doesn't include the customers that don't
have a representative.

 

[Allen Browne]

Actully, Rudi, it does cover those cases.

A customer who has no rep, has an entry in the Customer table, but none in
the CustomerRepresentative table.

A rep who has no customer (in transition?) has an entry in the
Representative table, but none in the CustomerRepresentative table.

When you create a query, you will need to use outer joins to get these
returned. See:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

 

[Allen Browne]

Actully, Rudi, it does cover those cases.

A customer who has no rep, has an entry in the Customer table, but none in
the CustomerRepresentative table.

A rep who has no customer (in transition?) has an entry in the
Representative table, but none in the CustomerRepresentative table.

When you create a query, you will need to use outer joins to get these
returned. See:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

 

[Rudi]

It works. Thank you very much, Allen. As I said I'm still wet behind
the eares when it comes to MS Access. Thank you again. I owe you a
Foster's

 

[Rudi]

It works. Thank you very much, Allen. As I said I'm still wet behind
the eares when it comes to MS Access. Thank you again. I owe you a
Foster's

 

[Rudi]

no wait. Another problem

I tryed to link the CustomerRepresentaive table to a combox. The
problem is that if I select a CustomerID, the displayed customer data
is allways the selected customer with his first Representative (even if
I select the (same) customer with the second (or third)
representative).

I think this is happening because every time I select a record I only
select the CustomerID. The selection is not telling the program which
Representative I selected so it allways display's just the firs
Representative.
I tryed to mend this by adding a ID_Number in the
CustomerRepresentative table, so that every record has a unique number
that sets the individual records apart. The new CustomerRepresentaive
table now looks like this:

ID_Number (Autonumber)
ID_Customer (Number)
ID_Representaive (Number)

Unfortunately this solution has another problem. The ID_Number (in the
CustomerRepresentative table) is an Autonumber fieled. And the
Autonumber field creates a number only for the entrys in the
CustomerRepresentative table. Thus the Customers without the
representatives (entrys that are only in the Customer table) don't get
a ID_Number and can not be selected in the Combobox.

HELP!

How can I make this work? How can I have a combobox that will let the
user select a certain customer with a certain representative?

 

[Allen Browne]

Your interface will be a main form bound to the Customer table, and a
subform bound to the CustomerRepresentative table. The subform will show
only those records that match the Customer in the main form. It will contain
a combo for selecting the representative for the customer. This combo's
RowSource will be the Representative table.

Perhaps you have a main form for the Representative, with a subform bound to
the CustomerRepresentative table. The subform will show only records that
match the Rep in the main form. It will contain a combo for selecting the
customer for the rep. This combo's RowSource will be the *Customer* table
(not the CustomerRep table.)

 

[Rudi]

I allready had my application set up the way you described. And it
works great.

Now I wanted to use this Customer, Rep, ... data in a new form called
Order. In this interface the user would input order info. And among the
order information the user would also select the specific customer and
one of his (if he has any) representatives via a combobox. Here is
where the problem lies. First I made a query that would include all the
Customer, Representative information (Customers without Rep, Customers
with Rep). So far so good. But when I bind this query to a combobox in
my Order form, the combobox selects only the CustomerID. This way, if
the Customer has more than one Rep, the user can't select the second or
third Rep. The selected CustomerID allways displays only the default,
first Representative.

I think this problem occures, becouse there is nothing to set the
entry's with the same Customer (and different Rep) apart. I tried to
mend this by creating a new table and using an append query (and delete
query), filling this new table with the query info. In the new table I
created a Autonumber for each entry that would set the "problematic"
entrys appart.

And it works OK but the problem is the Autonumber field. Everytime the
delete query deletes the old records and inserts the new, the
autonumber doesn't reset. The autonumbers start counting from the last
number that was deleted and not from 1.
I know that reseting the Autonumber requires me to compact the
database.

Is there a better way? Do you know of a code that would create a
sequence number for each entry? Or a different solution for my "Order
form combobox" problem? I know I'm a pain. I apologize in advance

Allen Browne je napisal:

 

[Allen Browne]

Your Order form has a CustomerID combo, and a RepID combo.
The CustomerID combo is bound to the Customer table, so that's easy.
You want to limit the RepID to only reps for the selected customer.

That will involve writing some code that changes the RowSource of the RepID
combo to an SQL statement that limits it to only reps for that customer. The
kind of code you need is illustrated in this article:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

If the RepID's bound column is visible (not zero-width), put the code into
the Enter event of the combo.

If it is zero-width, you need it to execute even when the combo does not
have focus, so use the RepID's Exit event to restore all values.
(Alternatively, you have to use the AfterUpdate event procedure of the
CustomerID combo, and also the Current event of the form, and also the Undo
event of the form (which has to use the OldValue of CustomerID.) And then
you still have trouble with the combo not displaying correctly, due to
timing in Access.)

 

[Rudi]

Good idea about the 2 comboboxes. Thank you.

Allen Browne je napisal: