Advice for field structure or query structure - not getting theresults I thought I should

[TPK]

I haven’t used Microsoft Access in a number of years, and then it was
for use on an IIS server where I used ASP to pull data. This time I
need to create a stand alone database.

I want to create a simple database to catalog different classes of
publications (around 600 documents) so that editors can search for and
find specific documents that contain information to be changed.

I have about 5 different kinds of document. For example, one group is
Word documents that are called “job aids”, another group is largely
PowerPoint documents used for instruction and another group is
composed of Excel documents.

Here is the approach I thought of using:
Create a Table (called Table1)
Create a Query (called qryCPT4)
Create a user form to supply the variable (called Form1)
The Table contains these fields:
1) docID (key field)
2) fileName: File Name (with extension, text field 255 character
limit)
3) docSUBJECT: Subject (meta data, text field 255 character limit)
4) docAUTHOR: Author (meta data, comma separated phrases, text field
255 character limit)
5) docKEYWORDS: Keywords (meta data, comma separated words, text field
255 character limit)
6) docCPT4: CPT-4 Codes (numeric only, 3 digits) (comma separated
numbers, 5 digit numbers, memo field 64,000 character limit*)
7) docCPTSpecific: CPT-4 Specifier (2 character alpha numeric) (text
field 255 character limit)
8) docDATE: Last modification date (date/time field)

SAs an example, some documents mention (within their text) multiple
CPT codes. Each code is a 3 digit number. Each document might mention
dozens of codes. Some documents might have the same codes, some not.

Ideally, there would be a Form front end to the database where a user
can enter (for example) a 3 digit CPT code and see a list of all the
documents that mention that code.

My problem seems that when I use this query: (qryCPT4)
Like “*”&[Form1]&”*”

I only get one record returned. I should be getting several. The
problem might be the query or it might be the way the contents of the
field are structured. Right now the contents of the “docCPT4” field
are numbers separated by commas no spaces (example:
999,997,998,621,423).
Any hints as to where I might have made an error?

Thank you,

TPK

 

[XPS350]

I haven’t used Microsoft Access in a number of years, and then it was
for use on an IIS server where I used ASP to pull data. This time I
need to create a stand alone database.

I want to create a simple database to catalog different classes of
publications (around 600 documents) so that editors can search for and
find specific documents that contain information to be changed.

I have about 5 different kinds of document. For example, one group is
Word documents that are called “job aids”, another group is largely
PowerPoint documents used for instruction and another group is
composed of Excel documents.

Here is the approach I thought of using:
Create a Table (called Table1)
Create a Query (called qryCPT4)
Create a user form to supply the variable (called Form1)
The Table contains these fields:
1)      docID (key field)
2)      fileName: File Name (with extension, text field 255 character
limit)
3)      docSUBJECT: Subject (meta data, text field 255 character limit)
4)      docAUTHOR: Author (meta data, comma separated phrases, textfield
255 character limit)
5)      docKEYWORDS: Keywords (meta data, comma separated words, text field
255 character limit)
6)      docCPT4: CPT-4 Codes (numeric only, 3 digits) (comma separated
numbers, 5 digit numbers, memo field 64,000 character limit*)
7)      docCPTSpecific: CPT-4 Specifier (2 character alpha numeric)(text
field 255 character limit)
8)      docDATE: Last modification date (date/time field)

SAs an example, some documents mention (within their text) multiple
CPT codes. Each code is a 3 digit number. Each document might mention
dozens of codes. Some documents might have the same codes, some not.

Ideally, there would be a Form front end to the database where a user
can enter (for example) a 3 digit CPT code and see a list of all the
documents that mention that code.

My problem seems that when I use this query: (qryCPT4)
Like “*”&[Form1]&”*”

I only get one record returned. I should be getting several. The
problem might be the query or it might be the way the contents of the
field are structured. Right now the contents of the “docCPT4” field
are numbers separated by commas no spaces (example:
999,997,998,621,423).
Any hints as to where I might have made an error?

Thank you,

TPK

You have to refer to a field on the form in your query. So not Form1
but:
Forms!Form1!YourField

Groeten,

Peter
http://access.xps350.com

 

[XPS350]

I haven’t used Microsoft Access in a number of years, and then it was
for use on an IIS server where I used ASP to pull data. This time I
need to create a stand alone database.

I want to create a simple database to catalog different classes of
publications (around 600 documents) so that editors can search for and
find specific documents that contain information to be changed.

I have about 5 different kinds of document. For example, one group is
Word documents that are called “job aids”, another group is largely
PowerPoint documents used for instruction and another group is
composed of Excel documents.

Here is the approach I thought of using:
Create a Table (called Table1)
Create a Query (called qryCPT4)
Create a user form to supply the variable (called Form1)
The Table contains these fields:
1)      docID (key field)
2)      fileName: File Name (with extension, text field 255 character
limit)
3)      docSUBJECT: Subject (meta data, text field 255 character limit)
4)      docAUTHOR: Author (meta data, comma separated phrases, text field
255 character limit)
5)      docKEYWORDS: Keywords (meta data, comma separated words, text field
255 character limit)
6)      docCPT4: CPT-4 Codes (numeric only, 3 digits) (comma separated
numbers, 5 digit numbers, memo field 64,000 character limit*)
7)      docCPTSpecific: CPT-4 Specifier (2 character alpha numeric) (text
field 255 character limit)
8)      docDATE: Last modification date (date/time field)

SAs an example, some documents mention (within their text) multiple
CPT codes. Each code is a 3 digit number. Each document might mention
dozens of codes. Some documents might have the same codes, some not.

Ideally, there would be a Form front end to the database where a user
can enter (for example) a 3 digit CPT code and see a list of all the
documents that mention that code.

My problem seems that when I use this query: (qryCPT4)
Like “*”&[Form1]&”*”

I only get one record returned. I should be getting several. The
problem might be the query or it might be the way the contents of the
field are structured. Right now the contents of the “docCPT4” field
are numbers separated by commas no spaces (example:
999,997,998,621,423).
Any hints as to where I might have made an error?

Thank you,

TPK

You have to refer to a field on the form in your query. So not Form1
but:
Forms!Form1!YourField

Groeten,

Peterhttp://access.xps350.com

By the way, it is not a good idea to put more than 1 bit of
information in one field.
For example Author. If there can be more than one other it is best to
create two extra tables, One with authors and one to store authors per
document. In that way there are no limits (what if the authors don't
fit in 255 positions) and you can make sure you always use the same
name for the same other. You can also make live easyer for the user.
Offer a combo here he can pick an author.
The same goes for keywords.

Groeten,

Peter
http://access.xps350.com

 

[Allen Browne]

The problem here is that fields 4, 5, 6, and 7 of your table each contain
multiple pieces of data. In a relational database, these should each be data
in a related table, so that one of your records can relate to many others
(one-to-many relationship.)

This idea is absolutely crucial, the heart of any relational database.

For example, since one document may have multiple authors, and one author
can write multiple documents over time, there is actually a many-to-many
relationship between documents and authors. This means you need 3 tables to
represent this data:
a) Document table (one record for each document, with a DocumentID primary
key)
b) Author table (one record for each author, with an AuthorID primary key)
c) DocumentAuthor table, with fields like this:
- DocumentID what document this entry refers to
- AuthorID what author was involved in this document.

So, if Document 5 involves 3 authors (say 2, 6, and 88), this 3rd table
would have 3 rows like this:
Document Author
5 3
5 6
5 88

In the same way, one document can have many keywords, so that's another
related table, and so on.

Then there's a whole lot more to learn about how to query these related
tables.

If it's any help, this web page explains how to build a library resource
type database:
http://allenbrowne.com/AppLibrary.html
It's dealing with questions that are different from what you are asking (how
to cope with not just books, but journals, DVDs, music, etc), but it does
illustrate the central idea of many-to-many relationships. In that example,
tblCreator is like (b) above, and tblResourceCreation is (c).

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I haven’t used Microsoft Access in a number of years, and then it was
for use on an IIS server where I used ASP to pull data. This time I
need to create a stand alone database.

I want to create a simple database to catalog different classes of
publications (around 600 documents) so that editors can search for and
find specific documents that contain information to be changed.

I have about 5 different kinds of document. For example, one group is
Word documents that are called “job aids”, another group is largely
PowerPoint documents used for instruction and another group is
composed of Excel documents.

Here is the approach I thought of using:
Create a Table (called Table1)
Create a Query (called qryCPT4)
Create a user form to supply the variable (called Form1)
The Table contains these fields:
1) docID (key field)
2) fileName: File Name (with extension, text field 255 character
limit)
3) docSUBJECT: Subject (meta data, text field 255 character limit)
4) docAUTHOR: Author (meta data, comma separated phrases, text field
255 character limit)
5) docKEYWORDS: Keywords (meta data, comma separated words, text field
255 character limit)
6) docCPT4: CPT-4 Codes (numeric only, 3 digits) (comma separated
numbers, 5 digit numbers, memo field 64,000 character limit*)
7) docCPTSpecific: CPT-4 Specifier (2 character alpha numeric) (text
field 255 character limit)
8) docDATE: Last modification date (date/time field)

SAs an example, some documents mention (within their text) multiple
CPT codes. Each code is a 3 digit number. Each document might mention
dozens of codes. Some documents might have the same codes, some not.

Ideally, there would be a Form front end to the database where a user
can enter (for example) a 3 digit CPT code and see a list of all the
documents that mention that code.

My problem seems that when I use this query: (qryCPT4)
Like “*”&[Form1]&”*”

I only get one record returned. I should be getting several. The
problem might be the query or it might be the way the contents of the
field are structured. Right now the contents of the “docCPT4” field
are numbers separated by commas no spaces (example:
999,997,998,621,423).
Any hints as to where I might have made an error?

Thank you,

TPK