Random Restrictions for reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am at a loss as to how to search for this so I couldn't find anything.

My users have a mailing list database (45,000 records and counting) with a
lot of "tags". These "tags" are used to define which mailing list the
person/business/?? is on. What they want is to have all these tags (78 of
them at last count) on a form and select any kind of combination of them.

I have a way to do this but it sure gets cumbersome every time I need to add
or delete a "tag" from the system.

I am hoping that there is a better way to do it...

Here is what I currently do.

On a form with all the tags (yesno fields but toggles on the form) on the
make report CMB

…
If Forms!fm_rpt_select![2dtog] = False Then
[2dtog] = "9"
End If
If Forms!fm_rpt_select![34BRtog] = False Then
[#$BRtog] = "9"
End If
If Forms!fm_rpt_select!ACBtog = False Then
ACBtog = "9"
End If
If Forms!fm_rpt_select!AIPPtog = False Then
AIPPtog = "9"
End If
….
and so on for each of the tags. Yes the setting all to 9 is not really
necessary but I am sure I had some logic in my head when I did this all those
years ago in A97…


The report is based on the following query (a subset for brevity)

SELECT Mum1ALL.LastName1, Mum1ALL.FirstName1, Mum1ALL.[E-Mail]
FROM Mum1ALL
WHERE (((Museum1ALL.[2D])=[forms]![fm_rpt_select]![2dtog])) OR
(((Mum1ALL.ACB)=[forms]![fm_rpt_select]![acbtog])) OR
(((Mum1ALL.AIPP)=[forms]![fm_rpt_select]![aipptog])) OR
(((Mum1ALL.ANFRM)=[forms]![fm_rpt_select]![anfrmtog])) OR
…
ORDER BY Mum1ALL.LastName1, Mum1ALL.FirstName1, Mum1ALL.CompanyName,
Mum1ALL.DepartmentName;


Is there a better way to do this (am sure there is) and an easier one so
that it is not so painful to add or delete tags out of the system?

TIA
Vanya
 
"Normalize", your data by removing the flag fields from the main record.
Create a table of related Records, each with its own Primary Key, but with a
Foreign Key to the Primary Key of the Mailing List, and a Foreign Key to the
Primary Key of a "Tag List" Table. Use a Multiselect List Box, or put a
True/False Field your Tag Table, to allow the user to select one or more
Tags to be reported.

Use VBA code to construct the WHERE clause of the Query that you use to
print your Label Report.

Larry Linson
Microsoft Access MVP
 
Back
Top