overview based on join table

I have a Customer table with CustomerID as PK, an Interest table with InterestID as PK and a Join Table CustomerInterests with a CPK made up of CustomerID & InterestID to handle the many to many relationship between the customers & interest groups.  Here's what I need:

1) A neat overview of customers that share a particular interest, and customers that don't.
2) I would like to be able to sort or filter these customers by features (eg last name, customer's employer, etc.).
3) I need to be able to add/remove a customer from the interest list, ie. the method should directly impact the Join Table records. If possible, I'd prefer a non VBA solution.
4)A plus, but not a necessity, would be a solution that displays all this information for several interests at the same time, not just one.

I'm not that familiar with access, but my intial thoughts were this:In a query, I'd have a row for each customer, and a column for each interest, and have an expression resulting in "true/false" or an "x" if the CustomerID&InterestID combination exists in the the Join Table. And then find some way to add/delete a Join Table record when a field is manually changed from True to False or viceversa.

I have no idea how to go about it. I use Access 2003.
LVL 4
kittenwhiskyAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Here is how I would handle #1.

See the queries and the reports.
Access-EEQ26624787CompareCustome.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
When you must list 4 separate "Requirements" then this is a tip-off that you really need to create 4 separate "Questions".

This reads more like a custom "Project" the a single question requiring one direct answer.

As you know from answering question, may experts may shy away from these Multi-part questions because it is never clear what the asker will consider the "Answer".

;-)

JeffCoachman
0
 
kittenwhiskyAuthor Commented:
thanks boag2000. I will rephrase my question. and thanks for the answer to part 1.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Oh,

...and BTW, I see you have some pretty impressive Excel VBA skills...
...Keep up the good work!

;-)

Jeff
0
All Courses

From novice to tech pro — start learning today.