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