Duplicating a Table 1

[unvme]

I have created a database that tracks "Projects". Each project has an Owner and a Distribution company. I want to be able to select the Owner and Distribution company from a "Client" table that I have created, via a combo box in my form. The problem I am running into is that I am not able to display the address, city, state, zip for both the Client and the Distribution company on my form, since they are both taken from the same "Client" table.

This probably isn't the smartest way to do it, but I was wondering if I can create a "Distribution" table that contains the same data as the "Clients" table. Right now I have a form to update and add new Clients. Is there a way I can have this form create the same data in the "Clients" and "Distribution" table all at once? Or is there an easier way I should approach this?

Thanks!

 

[randysmid]

Hi,
Do you have multiple projects for the same Owner and Distribution company? If so, you should add another table where the Owner_id AND Distribution_id AND Project_id are the keys. The combination of the 3 of these are unique.

By the way, having duplicated data makes your database "non-normalized", which is not good. If you had 25 records with addresses for the same distribution company, and their address changed, you would have to change this address 25 times. YUCK!!! Of course, you could write an update query to do this automatically. HTH,
Randy Smith
California Teachers Association

 

[unvme]

Thanks for the quick response Randy. To answer your question, yes, I have multiple projects for the same Owner and Distribution company. However, I'm having a hard time visualizing this relationship. Sorry, it's been a really long day. Could you explain how the "Clients" table would relate to this Owner-Distribution-Project table that I should create? And are you saying that I should have a table for "Owners" and "DistributionCompanies" that would both be drawing info from the "Clients" table? Sorry, I'm really confused now. Any help would be greatly appreciated.

Thanks!

Ryan

 

[randysmid]

Hi,
So, in this project table, you should have the Owner_id and Distribution_id. Is this true?

Now, here is what you wrote about the problem:
The problem I am running into is that I am not able to display the address, city, state, zip for both the Client and the Distribution company on my form, since they are both taken from the same "Client" table.

Why do the Owner and Distribution Company addresses exist in the Client table? Anyway, I will try to give you an idea of database design concepts, with a thought toward your tables.

First, you should have "master" tables. This would be your tables for Owners and Distribution. Information specifically pertaining to these should be placed here. That includes company name, addresses, phones, contacts, etc. When we look at your Projects table, it should only have ONE Owner, and ONE Distribution, correct?

Second, your Project Table needs to have the Project_id, plus the key fields for Owner and Distribution. Additional information pertaining ONLY to that specific project also goes into this table, such as StartDate, EndDate, bid Amount, etc., etc.

Third, the "CLient" table is totally irrelevant, because you will spend a lot of time trying to maintain it. This is because every time a new potential combination enters your system, you will need to add this "joint" information into the table. The Project table will link back to the Owner and Distribution tables, and is totally flexible in a form that creates a new Project.

Fourth, to get the addresses to appear for both the Client and Distribution on your form, you will want to create two queries, one for each. Then, you can add an unbound combo box on the form for each of them, e.g., cboOwner. Below the combo boxes, you would place labels (not textboxes) that correspond to the address, city, state, phone # info. Inside the AfterUpdate event for the combo box, you will place code that will load the appropriate information into the labels. Because they are labels, the user will not be able to change the data. Since you are only retrieving a single value for each field, you can use the DLookup function to retrieve things, such as street address, city, state, etc. The code in the After Update event for the Owner table might look like this (will fill in street):
lblStreet = DLookup("[Street]", "Owner", "[Owner_id] = '" & cboOwner.text & "'&quot
HTH,
Randy Smith
California Teachers Association

 

[randysmid]

OOOPs,
This line is incorrect:
Fourth, to get the addresses to appear for both the Client and .....

It should read:
Fourth, to get the addresses to appear for both the Owner and.... HTH,
Randy Smith
California Teachers Association

 

[unvme]

Randy,

The thing I am confused about is the "Client" table. You say that it is totally irrelevant, but here's my thought process:

I build a master list of all our clients. Then, for each project, I chose the project owner and distributor from that master list of clients. For any given project, a client could be an owner, distributor, or BOTH.

If I understand you correctly, you're saying to scratch the idea of creating a single Client table, and create 2 separate tables for Owners and Distribution companies. My thoughts on this idea are that the Owners and Distributors table will contain the exact same data, because our project owners are also distribution companies, and vise versa.

Sorry if I've completely misunderstood you.


Thanks!

Ryan

 

[randysmid]

Hi,
Yes, I didn't realize that an Owner could also be a Distributor. And, he/she could be both on any given project. In that case, the Clients table sounds like the most practical solution, and you would NOT have an Owner table nor a Distributor table.
In your form to create a new project, you will have 2 combo boxes (cboOwner and cboDistributor) appear for the selection of the Owner AND Distributor. These 2 combo boxes will be linked to the same query!!! As I said before, you will add labels representing the Owner address, city, state, etc. AND, you will add labels for the Distributor address, etc. Once the Owner has been selected, then you can fill in the addresses using the code I gave you. Do the same for the AfterUpdate event for cboDistributor.
Let me know if you need more help. HTH,
Randy Smith
California Teachers Association

 

[unvme]

Hi Randy,

I'm having some difficulty with the syntax of the dlookup function. Here is the code I placed in the "After Update" field of my Distribution Address field on my form:

=DLookUp("[ClientAddress]","tblClients","[ClientCode] = '" & [cboDistributionID] & "'&quot

Essentially, I want the Distribution Address to be displayed when a Distribution Company is selected from the Combo Box (cboDistributionID). I don't get any errors, but I do not get an address displayed either.

Any ideas?


Thanks!
Ryan

 

[randysmid]

Hi,
Is Distribution _ID numeric?

If so, then the DLookup must appear as follows:
=DLookUp("[ClientAddress]","tblClients","[ClientCode] = & [cboDistributionID]&quot

LOGIC = string datatypes must be encased in single quote marks, as the original example I gave. Numeric datatypes must not be encased in anything. Date datatypes must be encased in pound signs (#), unless the textbox is already designated as date.
HTH,
Randy Smith
California Teachers Association

 

[unvme]

Randy,

Both ClientCode and DistributionID are text fields. DistributionID is a field in the tblProjects table that references the ClientCode and ClientCompany fields from the tblClients table. And since ClientCode is of type text, I assigned DistributionID to text as well.

 

[randysmid]

Hi,
WRONG LOCATION!:
Your wrote: . Here is the code I placed in the "After Update" field of my Distribution Address field on my form This is not the correct spot for this code.

First, the Distribution Address field should be label, and not a text box (perhaps we could call it "lblDistributionAddress&quot.
Second, this code belongs in the After Update event for the Combo Box (cboDistributionID). The code here would look like this:
lblDistributionAddress.caption = =DLookUp("[ClientAddress]","tblClients","[ClientCode] = '" & [cboDistributionID] & "'&quot


HTH,
Randy Smith
California Teachers Association

 

[unvme]

THANK YOU!!!!!

I had to do some tweaking, but everything works great now. Here's the code I ended up with:

lblDistributionAddress = DLookup("[ClientAddress]", "tblClients", "[ClientCode] = '" & [cboDistributionID] & "'&quot

One question: I thought labels referred to the field that describes the text box.

ex: Address: ___________________

so "Address:" would be the label, which I would name lblAddress, and the "_____________" would be the text box, which I would name txtAddress. That is how I do it with combo boxes as well.

ex: Company: ___(combo box of all companies)___

so in this case, "Company:" would be named lblCompany, and the "__(combo box)__" field would be named cboCompany. Is this incorrect?

 

[unvme]

Disregard my previous question. I'm an idiot. I do have one other problem now though. Whenever I go to another project (previous record, next record, new record) in my form, the Distribution Address, City, State and Zip remain the same. Here is my code:


Private Sub cboDistributionID_AfterUpdate()

lblDistributionAddress = DLookup("[ClientAddress]", "tblClients", "[ClientCode] = '" & [cboDistributionID] & "'&quot
lblDistributionCity = DLookup("[ClientCity]", "tblClients", "[ClientCode] = '" & [cboDistributionID] & "'&quot
lblDistributionState = DLookup("[ClientState]", "tblClients", "[ClientCode] = '" & [cboDistributionID] & "'&quot
lblDistributionZipcode = DLookup("[ClientZipcode]", "tblClients", "[ClientCode] = '" & [cboDistributionID] & "'&quot

End Sub


Is there a way I can code these entries so that they relate to an individual project - so that the correct information is displayed for each project, and if I go to a new record, the data does not remain there?


Thanks!
Ryan

 

[unvme]

Nevermind...

Figured it out. Just threw the above code into the "On Current" event.