need help!!

[jamiepgs]

It's been a while since I learned how to use Access 2003, and I have some
trouble.

I have made a checklist wich I check if a document is found in a certain
folder. If a document isn't found, a letter has to be sent to a provider to
send a copy of the document. To make my life easier, I've made a mail-merge
document.

I need to create a query that will display the options that are false only.
"=no" doesn't work because it only shows records where all documents are
missing, but the documentsmissing isn't the same.

Also, when I enter the field into the mail-merge, it show true or false, is
there a way thatwill display the name is the value is false?

Thanks a bunch

 

[BruceM]

Did you use "=no" with quotes as the criteria for the field? If so, try
using False (without quotes) as the criteria.

Without more information it is difficult to know just what you are trying to
do. You mention A Document and a Certain Folder. Are the document and the
folder names incorporated into the database? How many tables are there,
what is their general structure, and how are they related?

When asking about a query it is often helpful to post its SQL. To do that,
open the query, then click View > SQL. Copy what you see and paste it into
a message. Describe what you expect and what is happening instead, and
include any error messages you have received and the cirumstances under
which you received them.

 

[Ken Sheridan]

You don't say how your data is structured, but it sounds to me like you have
a many-to-many relationship between documents and folders, i.e. each document
can be in one or more folders, and each folder can contain one of more
documents. To model this you'd need three tables:

1. Documents: DocumentID, DocumentName etc.

2. Folders: FolderID, FolderName etc

3. FolderDocuments: This table models the many-to-many relationship
between the first two by having two foreign key columns FolderID and
DocumentID which reference the primary keys of the other two tables
respectively. It would also have a Boolean (Yes/No) Present column which
records if the document is present in the folder, i.e. the table would have
rows for all documents which SHOULD be in a folder, but only set to True for
those which ARE in the folder. Data entry could be either by a Folders form
with a Documents subform, in continuous form view, embedded in it, or
conversely, by a Documents form with a Folders subform, again in continuous
form view, embedded in it

Schematically the model would look like this, with the < and > signs
representing the 'many' ends of the two on-to-many relationships into which
the many-to-many relationship is resolved:

Folders----<FolderDocuments>----Documents

With the above model the absence of any documents from a folders would be
returned by a query like this:

SELECT FolderName, DocumentName
FROM Folders INNER JOIN
(Documents INNER JOIN FolderDocuments
ON Documents.DocumentID = FolderDocuments.DocumentID)
ON Folders.FolderID = FolderDocuments.FolderID
WHERE NOT Present
ORDER BY FolderName, DocumentName;

You can of course restrict the result set to a particular document and/or
folder by including further criteria in the WHERE clause. If you want to
show both present and absent documents omit the 'NOT Present, criterion from
the WHERE clause and include the Present column in the SELECT clause.

Remember that if you include spaces or other special characters in table or
column names you must enclose them in brackets [like this] in the query.

Ken Sheridan
Stafford, England

 

[jamiepgs]

I don't think that is how it is, I'll give you a link to a dummy database
(the real one contains confidential information about real people) Also, I
may have been misleading a bit. By documents, I mean paperwork. I have a
portfolio containing details about a person. Part of the work I do is
ensuring that all required documents about any given person is available.
This database is just an easy way to "check".

http://www.wikiupload.com/download_page.php?id=193208 - dummy db

You don't say how your data is structured, but it sounds to me like you have
a many-to-many relationship between documents and folders, i.e. each document
can be in one or more folders, and each folder can contain one of more
documents. To model this you'd need three tables:

1. Documents: DocumentID, DocumentName etc.

2. Folders: FolderID, FolderName etc

3. FolderDocuments: This table models the many-to-many relationship
between the first two by having two foreign key columns FolderID and
DocumentID which reference the primary keys of the other two tables
respectively. It would also have a Boolean (Yes/No) Present column which
records if the document is present in the folder, i.e. the table would have
rows for all documents which SHOULD be in a folder, but only set to True for
those which ARE in the folder. Data entry could be either by a Folders form
with a Documents subform, in continuous form view, embedded in it, or
conversely, by a Documents form with a Folders subform, again in continuous
form view, embedded in it

Schematically the model would look like this, with the < and > signs
representing the 'many' ends of the two on-to-many relationships into which
the many-to-many relationship is resolved:

Folders----<FolderDocuments>----Documents

With the above model the absence of any documents from a folders would be
returned by a query like this:

SELECT FolderName, DocumentName
FROM Folders INNER JOIN
(Documents INNER JOIN FolderDocuments
ON Documents.DocumentID = FolderDocuments.DocumentID)
ON Folders.FolderID = FolderDocuments.FolderID
WHERE NOT Present
ORDER BY FolderName, DocumentName;

You can of course restrict the result set to a particular document and/or
folder by including further criteria in the WHERE clause. If you want to
show both present and absent documents omit the 'NOT Present, criterion from
the WHERE clause and include the Present column in the SELECT clause.

Remember that if you include spaces or other special characters in table or
column names you must enclose them in brackets [like this] in the query.

Ken Sheridan
Stafford, England

It's been a while since I learned how to use Access 2003, and I have some
trouble.

I have made a checklist which I check if a document is found in a certain
folder. If a document isn't found, a letter has to be sent to a provider to
send a copy of the document. To make my life easier, I've made a mail-merge
document.

I need to create a query that will display the options that are false only.
"=no" doesn't work because it only shows records where all documents are
missing, but the documentsmissing isn't the same.

Also, when I enter the field into the mail-merge, it show true or false, is
there a way thatwill display the name is the value is false?

Thanks a bunch

 

[Ken Sheridan]

With your dummy database the following query will return all rows from the
Sheet 1 table where any document is missing, showing the missing documents as
a comma separated list in the Missing Documents column:

SELECT Name, Providor, MID(
IIF(NOT[Document 1],", Document 1","") &
IIF(NOT[Document 2],", Document 2","") &
IIF(NOT[Document 3],", Document 3","") &
IIF(NOT[Document 4],", Document 4","") &
IIF(NOT[Document 5],", Document 5",""),3)
AS [Missing Documents]
FROM Sheet1
WHERE NOT([Document 1] AND
[Document 2] AND
[Document 3] AND
[Document 4] AND
[Document 5]);

However, I'd recommend that you consider normalizing the database along the
lines I suggested in my original reply. At present you are using Access more
like a spreadsheet application than a relational database. Having each
document as a column heading is what's known as 'encoding data as column
headings', whereas in a relational database data should be stored only as
values at column positions in rows in tables.

Ken Sheridan
Stafford, England

I don't think that is how it is, I'll give you a link to a dummy database
(the real one contains confidential information about real people) Also, I
may have been misleading a bit. By documents, I mean paperwork. I have a
portfolio containing details about a person. Part of the work I do is
ensuring that all required documents about any given person is available.
This database is just an easy way to "check".

http://www.wikiupload.com/download_page.php?id=193208 - dummy db

You don't say how your data is structured, but it sounds to me like you have
a many-to-many relationship between documents and folders, i.e. each document
can be in one or more folders, and each folder can contain one of more
documents. To model this you'd need three tables:

1. Documents: DocumentID, DocumentName etc.

2. Folders: FolderID, FolderName etc

3. FolderDocuments: This table models the many-to-many relationship
between the first two by having two foreign key columns FolderID and
DocumentID which reference the primary keys of the other two tables
respectively. It would also have a Boolean (Yes/No) Present column which
records if the document is present in the folder, i.e. the table would have
rows for all documents which SHOULD be in a folder, but only set to True for
those which ARE in the folder. Data entry could be either by a Folders form
with a Documents subform, in continuous form view, embedded in it, or
conversely, by a Documents form with a Folders subform, again in continuous
form view, embedded in it

Schematically the model would look like this, with the < and > signs
representing the 'many' ends of the two on-to-many relationships into which
the many-to-many relationship is resolved:

Folders----<FolderDocuments>----Documents

With the above model the absence of any documents from a folders would be
returned by a query like this:

SELECT FolderName, DocumentName
FROM Folders INNER JOIN
(Documents INNER JOIN FolderDocuments
ON Documents.DocumentID = FolderDocuments.DocumentID)
ON Folders.FolderID = FolderDocuments.FolderID
WHERE NOT Present
ORDER BY FolderName, DocumentName;

You can of course restrict the result set to a particular document and/or
folder by including further criteria in the WHERE clause. If you want to
show both present and absent documents omit the 'NOT Present, criterion from
the WHERE clause and include the Present column in the SELECT clause.

Remember that if you include spaces or other special characters in table or
column names you must enclose them in brackets [like this] in the query.

Ken Sheridan
Stafford, England

It's been a while since I learned how to use Access 2003, and I have some
trouble.

I have made a checklist which I check if a document is found in a certain
folder. If a document isn't found, a letter has to be sent to a provider to
send a copy of the document. To make my life easier, I've made a mail-merge
document.

I need to create a query that will display the options that are false only.
"=no" doesn't work because it only shows records where all documents are
missing, but the documentsmissing isn't the same.

Also, when I enter the field into the mail-merge, it show true or false, is
there a way thatwill display the name is the value is false?

Thanks a bunch