Selecting multiple entries in a field Access 2003 -- I am rusty 3

[cbpwc01]

I am creating an Access 2003 database, and I haven’t built a database sine 2005 so my skills are very rusty.

My main table is set up with the following fields:

Client_Name
Product
Consultant
Consultant_Name
Primary_Contact
Secondary_Contact
Notes

The field “Product” is set up as a lookup from the table “Product_List”, where I have all of the firm’s products.

Right now I can only select one product at a time, and in many cases a client may be in two or more products.

Any suggestions on a solution?

Thanks!

Chris

 

[PWise]

the lookup should be

Select 0 ,"all products"
union
select productid,productname
from products

 

[cbpwc01]

pwise,

I keep getting the error "The number of columns in the two selected tables or queries of a union query do not match. (Error 3307)"

This is the code as I entered it:

Select 0,"Fund" from Products
UNION select Fund
from Products;


Products is the table, Fund is the only field in the Products table (and the primary key). Thank you for your help--It's definitely brushing off some cobwebs for me.

Chris

 

[fneily]

In your first post, you say the table's name is Product_list then in another it's Products. It would help to post your table structure, name and fields names and designated primary key, to avoid confusion.

Here's a little refresher reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

One client can have many products. One product can be for many clients. A many-to-many relationship that relational databases like Access don't like. You need a junction table.
Questions: is a consultant for a specific client or dependant on the product? Does Primary/Secondary_Contact relate to the client? I'll assume contacts are for Client and consultants are for products.
So:
tlbLClients
ClientID Primary Key
Primary_Contact
Secondary_Contact
other Client info

tblConsultants
ConsultantID Primary Key
Consultant_Name
Consultant info fields

tblProducts
ProductID Primary Key
Other product fields

tblTransaction
TransID Primary Key
ClientID Foreign Key
ProductID Foreign Key
ConsultantID Foreign Key
Notes
Date
Other COMMON fields

Now the form for tblTransaction can have comboboxes for the ID's. And one client can have many products.

 

[cbpwc01]

Here is the full database structure. Primary Keys I denote with an *, Foreign keys I denote with a #

For reference, Clients may or may not have a consultant contact, so they are a client field along with the primary and secondary contact. The products table I list a product and the services associated with that product, as well as the Relationship Manager and Support professional. Some tables I use exclusively for lookups in the client or product table (Schedule, RM, RM Support).

Client_Table (TABLE)
Client_Name*
Product#
Consultant_ID#
Consultant
Consultant_Name#
Primary_Contact
Secondary_Contact
Client_Notes
Client_Visit(Schedule)
Client_Visit(Date)
Client_Call(Schedule)
Client_Call(Date)


Products (TABLE)
Product*
Q1_Report
Q2_Report
Q3_Report
Q4_Report
Advisory_Board_Meeting
Annual_Meeting
RM#
RM_Support#


Consultant_Addresses (TABLE)
Consultant_ID*
Consultant
Address1
Address2
Address3
Address4
City
State
Zip
Country

Consultant_Contacts(TABLE)
Consultant_ID#
Consultant
Consultant_Contact*
Phone
Email

Client_Contacts (Table)
Client_Name#
Contact_Name*
Email
Phone
Advisory_Board_Member

Schedule (Table)
Schedule*

RM (Table)
RM*

RM_Support (Table)
RM_Support*


It almost looks like the client table should feed into a summary table so I can have my comboboxes there, am I correct? Thus, the "transaction" table would be as follows:

Client_Overview (Table)
Client_ID*
Client_Name#
Product#
Client_Notes
Client_Visit(Schedule)
Client_Visit(Date)
Client_Call(Schedule)
Client_Call(Date)

Am I correct??

 

[JoeAtWork]

I would suggest that primary keys should just be some random number that has no meaning to the user, not something descriptive like "Client_Name" or "Product".

Benefits are:

1. Allows you to change the descriptive name in only one place. For example, if your client "Mary Jones" marries someone with the last name "Smith", you only need to change the name in a single record in a single table. The way you have it now there are multiple tables and records where you would have to update Client_Name.

2. Allows you to have the records with the same name but still have a unique identifier. You may at some point have two clients named "John Smith".

 

[fneily]

As JoeAtWork suggested, make up your own Primary Keys.

Qestions:
Client_Visit(Schedule), Client_Visit(Date), Client_Call(Schedule), Client_Call(Date) - are they related to seeing a product? I'll assume so.

What's the relationship between Client and Client Contact? One-to-many or many-to-many?

Are the Quarterly reports for a single Product? Are they produced at the Advisory_Board_Meeting? May want a table called Meetings.

Products table is for product info. Read the reference I posted. Each table should contain only information for the topic of that table. In cases of many-to-many relationships, you need to build an intermediate table(junction table).

Rearranged some of the tables:
Client_Table (TABLE)
Client_ID Primary Key
Client_Name
Primary_Contact
Secondary_Contact

Products (TABLE)
Product_ID Primary Key
Product

Consultant_Addresses (TABLE)
Consultant_AddID Primary Key
Consultant_ID
Address
City
State
Zip
Country

Consultant_Contacts(TABLE)
Consultant_ID Primary Key
Consultant
Phone
Email

Client_Contacts (Table)
Client_ContactID Primary Key
Client_ID
Contact_Name
Email
Phone
Advisory_Board_Member

Schedule (Table)
Schedule_ID Primary Key
Schedule

RM (Table)
RM_ID Primary Key
RM Info fields

RM_Support (Table)
RM_Support_ID Primary Key
RM_Support Info Fiels

Client_Overview (Table)
Client_Ov_ID Primary Key
Client_ID
Product_ID
Consultant_ID
Client_Notes
Client_Visit(Schedule)
Client_Visit(Date)
Client_Call(Schedule)
Client_Call(Date)

You now need a table to bring Product together with RM and RMSupport.

I have other questions, but you should be able to see how things are starting to relate to each other.

 

[cbpwc01]

I will add the numeric IDs and rearrange the tables as suggested, but I think I need the consultant_contact on the client_table as well since it is a contact specific to a client just like the "primary_contact" and "secondary_contact"..

Answers to questions:
Client_Visit(Schedule), Client_Visit(Date), Client_Call(Schedule), Client_Call(Date) - are they related to seeing a product? I'll assume so.

These are services we may or may not provide a client on an individual basis in addition to the standard product services. Basically, we may want to visit X client annually, and I'll store both the ideal frequency Client_Visit(Schedule) and the most recent visit Client_Visit(Date). This will vary by client, thus why I had them on the client summary table.

What's the relationship between Client and Client Contact? One-to-many or many-to-many?

Each client has between zero and two contacts (primary contact, secondary contact) and a separate consultant contact


Are the Quarterly reports for a single Product? Are they produced at the Advisory_Board_Meeting? May want a table called Meetings.

Each of these are standard services we provide investors in products, and the field is completed with a date (such as July 15th) each is due. Thus, Fund X issues Q reports in April, July, Octover and February and Fund X has an advisory board meeting in September and an annual meeting in December. I can query what is due in a specific time frame and manage project flow easier this way. I assume since this is product-specific, this goes on the products table--am I wrong?


Products table is for product info. Read the reference I posted. Each table should contain only information for the topic of that table. In cases of many-to-many relationships, you need to build an intermediate table(junction table).

Thanks!!

Chris

 

[cbpwc01]

To link the Products and RM/RM support, I made a Product_Overview Table:

Product_OvID
Product_ID
RM_ID
RM_SupportID

I think the quarterly report information belongs here as well and added those accordingly. Am I wrong?

Chris

 

[fneily]

The Product_Overview table looks good. That table has to do with connecting Regional Managers and Support to a Product. It doesn't have anything to do with reports or meetings. So the report and meeting dates I'd put in the Product_table. As long as they are Product specific and no other product uses the same info.

"I think I need the consultant_contact on the client_table as well since it is a contact specific to a client just like the "primary_contact" and "secondary_contact". Well, yes and no. Primary_contact and secondary_contact, to me, means if you can't get hold of the CLIENT then try them. The Consultant is NOT specific to ONE client. A Consultant can be assigned a number of clients. So it should not be in the Client_Table, but the Summary. Now, if the Primary_Contact and Secondary_Contact are NOT substitutes for the Client, then they are also NOT in the client table. Comes down to definition.

By the way, it's not if you're wrong or right, as you keep asking. It's does it follow the rules of Normalization. Sometimes, that's a judgement call.

 

[cbpwc01]

Thank you all for your help. The database is 85% of the way there and I am working on the first major form, which will be the main interface and data management place.

All my fields work perfectly except the pesky products, which still displays only one product per client, not all the products the client is invested in.

The SQL I am using for my listbox query is as follows:

SELECT Products.Product, Client_Overview.Client_ID
FROM Products INNER JOIN Client_Overview ON Products.Product_ID = Client_Overview.Product_ID;

Where am I going wrong? Should I use a subform? I tried that, but it only wants to link to the product ID, and some clients link to more than one product ID.

Thanks Again!

Chris

 

[fneily]

Your query should have the following thre tables in design view:
Client_table Client_overview Products

Client_table is connect to Client_overview through Client_ID.

Client_overview is connect to Products through Product_ID.

 

[cbpwc01]

I used the wizard to set it up and I now show both joins. However, my results list every product entry in the database--one product shows up 26 times instead of the products associated with one client ID showing up.

I assume I'll use the query for the listbox.

This is the SQL as I have it:

SELECT Products.Product
FROM Products INNER JOIN (Client_Table INNER JOIN Client_Overview ON Client_Table.Client_ID = Client_Overview.Client_ID) ON Products.Product_ID = Client_Overview.Product_ID;

I really wish I had gone to school as a programmer or could get paid to take SQL lessons.

 

[fneily]

You don't have a where condition. Don't use the wizard. Go directly to Design view and select the three tables. If you spelled everything correctly, you should see them connected. Then bring down into the grid Client_ID, Client_name, Product.
You can then run the query directly with a criteria or you can place a criteria on Client_ID that's a listbox selection. It'll look like:
Forms![FormNameListBoxIsOn]![ListBoxName]

The general SQL of the query is:
SELECT Client_Table.Client_ID, Client_Table.Client_Name, Product.Product_ID, Product.Product
FROM (Client_Table INNER JOIN Client_Overview ON Client_Table.Client_ID = Client_Overview.Client_ID) INNER JOIN Product ON Client_Overview.Product_ID = Product.Product_ID
WHERE (((Client_Table.Client_ID)="cnicola"));

You should be using the QBE pane for queries. Then you can look at the SQL generated by Access to learn SQL.

 

[cbpwc01]

Thank you! I believe the first cut of the database is final, and I'll work more to get a better grip on the query function so that I can cut data in more ways.

 

[cbpwc01]

Argh-- one problem. Now that I populate this, it is showing all products in the database no matter which client I am viewing on the form and not highlighting or indicating which products the client is in.

This is my .sql:

SELECT Products.Product, Client_Overview.Client_ID, Client_Table.Client_Name, Products.Product_ID
FROM (Client_Table INNER JOIN Client_Overview ON Client_Table.Client_ID = Client_Overview.Client_ID) INNER JOIN Products ON Client_Overview.Product_ID = Products.Product_ID
WHERE (((Client_Overview.Client_ID)=[Forms]![Client_Overview]![Combo77]));

Any idea where I am going wrong? I've messed with this for a few hours to no avail.

Thanks!

 

[fneily]

WHERE (((Client_Overview.Client_ID)
The criteria is on the Client_table, not Client_overview.