Full Version: Issues In My Continuous Form
UtterAccess Forums > MicrosoftŪ Access > Access Forms
I have a continuous form that I have been asked to modify. It's a correspondence history form where our employees make notes about phone calls they get from examinees. They want to add up to 3 categories for each call. The first issue I'm having is that instead of showing a record for one call with 3 categories assigned, shown by combo boxes, it shows 3 records, one for each category, all for the same call.

My query for the form looks like this, but I know it isn't right, because it's showing a different record for each category assigned to a call (see above).

SELECT Correspondence.ExamineeNo, Correspondence.TimeStamp, Correspondence.Employee, Correspondence.Note, Correspondence.CommunicationType, dbo_lkpCallCategory.Category
FROM dbo_lkpCallCategory RIGHT JOIN Correspondence ON (dbo_lkpCallCategory.TimeStamp = Correspondence.TimeStamp) AND (dbo_lkpCallCategory.ExamineeNo = Correspondence.ExamineeNo);

The second issue is, the 3 combo boxes I have for Category (cboCategory1, 2, and 3) all have to represent one field, Category, so they are unbound. I'm having trouble getting the different calls to show their own categories. I tried this as the control source for the combo box but this is not working either.

=DLookUp("[Category]","dbo_lkpCallCategory","([TimeStamp]= #" & [Me].[TimeStamp] & "#) And ([ExamineeNo] = " & [Me].[ExamineeNo] & ")")

I've attached a screenshot of the form so you can get an idea what I'm talking about.

Thanks in advance,
Click to view attachment
It's called a cross-product or Cartesian product.

I imagine it's because you have 3 combo boxes, or at any rate something in the query that is not related to a single item in the phone log. Because of that each item in the phone log gets related to each OF THE items in the unbound table, and in your case you end up seeing 3 of everything.

It's a normalisation issue.

In order to add multiple categories you need another bound table joining each call to the categories you want to allocate to this call.
Either that or a multi-value-field, which is a recent access "special feature", although I doubt if too many of us would recommend using it.
Are you wanting to assign multiple categories to a correspondence or one category per correspondence?

In either case, you should NOT be including the Category lookup in the Recordsource. You need to either add a CategoryID FK to the Correspondence table or add a junction table if you need to select multiple categories per correspondence.
Ok, I removed the lookup table from the record source and that removed the multiple records for each call. My senior programmer told me a lookup table was the way to get this done, before leaving on vacation for 2 weeks. Guess I didn't understand how she intended me to use the lookup table. I don't have access to add an FK to the Correspondence table. I know she suggested a lookup table so we would not have to use a multivalue field.

There are multiple categories (up to 3) assigned to each call.
A lookup table IS the way to go. But its only one piece of the puzzle. Since you have a many to many relationaship (1 category can apply to multiple correspondence and 1 correspondence can have multiple categories you need a junction table:

CorrCatID (PK Autonumber)
CorrespondenceID (FK)
CategoryID (FK)
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.