JackieR said:
I have a table that stores documents (like a document library). Each
document is a "record". Each document can be associated with one or many
business groups in the company. I have a column for "business group" in my
table.
But that column (= field in each record) will hold 0 or 1 business
group, but not 2.
There are about 40 (and counting) business groups that we prepare
documents for. Each document can be associated with one or any combination of
business groups depending on the content of the document.
And each business group is likely to have many documents. An easy way
to take care of this is to set up another Table in which each record
refers to some business group and to a document that the business group
uses. You might want additional information there, but those 2 fields
(plus maybe a primary-key field) would be sufficient.
I need to reflect
that for each record and then be able to run queries to tell me how many
documents I prepared for a single business goup or selection of business
groups. What I need to find out is the best way to set up this data. I
started using a "lookup" because I wanted to ensure that the data entry was
valid (no typos) but I need to be able to associate multiple groups with each
record (document).
I think I'd use two lookup queries, one to identify a business group,
and one to identify a document.
I hope this clarifies a little better. Please let me
know the best way to do this. Thank you, Jackie
Here's an example:
Table [BusinessGroup]:
BusinessGroupID Name
--------------- ------------
-1276678943 Finance
515918222 Marketing
1197638135 Engineering
Table [Documents]:
DocumentsID Name DateEntered
----------- -------------- -----------
-137051540 Budget, 2005 Q2 2/17/2005
907228413 Price list, 2006 10/7/2005
Based on these 2 Tables, we set up a new [BusGrpDocs] Table (without
lookup fields, making it look kinda hairy):
BusGrpDocsID BusinessGroupID DocumentsID DateAdded
------------ --------------- ----------- ---------
-2058202824 1197638135 907228413 10/7/2005
-2037286362 515918222 -137051540 3/1/2005
-1703897383 -1276678943 -137051540 2/17/2005
341604211 515918222 907228413 10/7/2005
Each record in this Table includes a reference to some business group in
[BusinessGroup] and to some document in [Documents], but the reference
values (foreign keys in this Table) are not very meaningful to a human
being. So, let's define some lookup queries to make this easier to use.
Each lookup query will include a key value in the first field and a
meaningful summary of the record's contents in the second field.
[Q_LookupBusGrp]:
SELECT [BusinessGroup].[BusinessGroupID],
[BusinessGroup].[Name]
FROM BusinessGroup
ORDER BY [BusinessGroup].[Name];
In Datasheet View, it looks like this, so you know to which group the
"1197638135" record belongs:
BusinessGroupID Name
--------------- ------------
1197638135 Engineering
-1276678943 Finance
515918222 Marketing
Similarly, the [Q_LookupDocuments] Query is defined in this way:
SELECT Documents.DocumentsID,
[Documents]![DateEntered] & " "
& [Documents]![Name] AS [Key]
FROM Documents
ORDER BY Documents.Name;
.... and in Datasheet View it looks like this, and even though the date
is listed first, its records are sorted by name (you have lots of
freedom in how you design this):
DocumentsID Key
----------- --------------------------
-137051540 2/17/2005 Budget, 2005 Q2
907228413 10/7/2005 Price list, 2006
Now, to apply the lookup Queries, we open [BusGrpDocs] in Table Design
View and select the [BusinessGroupID] foreign-key field. Click on the
Lookup tab and change the properties as follows:
Display Control: List box
Row Source: Q_LookupBusGrp
Column Count: 2
Column Widths: 0;1
Also, click on the other foreign key, [DocumentsID], and do the same
thing, except to set its Row Source to [Q_LookupDocuments]. Now, when
you open it in Datasheet View, it looks like this:
[BusGrpDocs] with lookup fields:
BusGrpDocsID BusinessGroupID DocumentsID DateAdded
------------ --------------- -------------------------- ---------
-2058202824 Engineering 10/7/2005 Price list, 2006 10/7/2005
-2037286362 Marketing 2/17/2005 Budget, 2005 Q2 3/1/2005
-1703897383 Finance 2/17/2005 Budget, 2005 Q2 2/17/2005
341604211 Marketing 10/7/2005 Price list, 2006 10/7/2005
Although you may need to include the primary key, [BusGrpDocsID], in
this Table, for use by other Tables, I think I would hide it in
Datasheet View, since there's no need to change it during data entry
(it's an Autonumber field and is set automatically) and there's no need
for a human being to look at it (it's used only as a reference
mechanism). If you hide it, what you'll see will look like this:
[BusGrpDocs] data-entry fields:
BusinessGroupID DocumentsID DateAdded
--------------- -------------------------- ---------
Engineering 10/7/2005 Price list, 2006 10/7/2005
Marketing 2/17/2005 Budget, 2005 Q2 3/1/2005
Finance 2/17/2005 Budget, 2005 Q2 2/17/2005
Marketing 10/7/2005 Price list, 2006 10/7/2005
.... but the values actually stored in this Table are the ones shown above.
Although you can use the Datasheet View to insert and edit records in
this Table, you may find it more convenient to set up a Form to do this
(your choice).
With the [BusGrpDocs] Table in place, you can new set up Queries to
count or list the documents for any business group, or list the business
groups that are using a given document.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
