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

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

Post by TPK » Sat, 10 Jul 2010 04:49:57


I haven 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 ob 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 ocCPT4field
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
 
 
 

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

Post by XPS35 » Sat, 10 Jul 2010 05:04:32


> 2) ileName: File Name (with extension, text field 255 charac>er
> lim>t)
> 3) ocSUBJECT: Subject (meta data, text field 255 character l>mit)
> 4) ocAUTHOR: Author (meta data, comma separated phrases, tex> field
> 255 character>limit)
> 5) ocKEYWORDS: Keywords (meta data, comma separated words, t>xt field
> 255 charact>r limit)
> 6) ocCPT4: CPT-4 Codes (numeric only, 3 digits) (comm> separated
> numbers, 5 digit numbers, memo field 64,000 charac>er limit*)
> 7) ocCPTSpecific: CPT-4 Specifier (2 character alpha n>meric) (text
> field 255 cha>acter limit)
> 8) ocDATE: Last modification date (d>te>time field)
>
> SAs an example, some documents mention (within their>text) multiple
> CPT codes. Each code is a 3 digit number. Each documen> might mention
> dozens of codes. Some documents might have the same c>de>, some not.
>
> Ideally, there would be a Form front end to the databa>e where a user
> can enter (for example) a 3 digit CPT code and see a >ist of all the
> documents that men>io> that code.
>
> My problem seems that when I use this q>ery: (qryCPT4)
> L>ke>[Form1]& >
> I only get one record returned. I should be g>tting several. The
> problem might be the query or it might be the way >he contents of the
> field are structured. Right now the contents of>the ocCPT4field
> are numbers separated by commas>no spaces (example:
> 9>9,997,998,621,423).
> Any hints as to where I might>ha>e made an err>r?> >
> 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://www.yqcomputer.com/

 
 
 

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

Post by XPS35 » Sat, 10 Jul 2010 05:20:14

n 8 jul, 22:04, XPS350 < XXXX@XXXXX.COM > wrote:
> > lim>t>
> > 3) ocSUBJECT: Subject (meta data, text field 255 character l>m>t)
> > 4) ocAUTHOR: Author (meta data, comma separated phrases, tex> >ield
> > 255 character>l>mit)
> > 5) ocKEYWORDS: Keywords (meta data, comma separated words, t>x> field
> > 255 charact>r>limit)
> > 6) ocCPT4: CPT-4 Codes (numeric only, 3 digits) (comm> >eparated
> > numbers, 5 digit numbers, memo field 64,000 charac>e> limit*)
> > 7) ocCPTSpecific: CPT-4 Specifier (2 character alpha n>m>ric) (text
> > field 255 cha>a>ter limit)
> > 8) ocDATE: Last modification date (d>te>t>me field)
>
> > SAs an example, some documents mention (within their>t>xt) multiple
> > CPT codes. Each code is a 3 digit number. Each documen> >ight mention
> > dozens of codes. Some documents might have the same c>de>,>some not.
>
> > Ideally, there would be a Form front end to the databa>e>where a user
> > can enter (for example) a 3 digit CPT code and see a >i>t of all the
> > documents that men>io> >hat code.
>
> > My problem seems that when I use this q>e>y: (qryCPT4)
> > L>ke>gt;[Form1]& >
> > I only get one record returned. I should be g>t>ing several. The
> > problem might be the query or it might be the way >h> contents of the
> > field are structured. Right now the contents of>t>e ocCPT4field
> > are numbers separated by commas>n> spaces (example:
> > 9>9>997,998,621,423).
> > Any hints as to where I might>ha>e>made an error>
>
> > TPK
>
> You have to refer to a field on the form in your>query. >o not Form1
> but:
> F>rm>!Form1!Your>ie>d
>
> 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
 
 
 

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

Post by Allen Brow » Sat, 10 Jul 2010 15:43:05

he 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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"TPK" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
>
> I only get one record returned. I should be getting several. >he
> problem might be the query or it might be the way the contents of >he
> field are structured. Right now the contents of the ocCPT4f>eld
> are numbers separated by commas no spaces (exam>le:
> 999,997,998,621,4>3).
> Any hints as to where I might have made an er>or>
>
> Thank >ou>
>
> TPK