DB Design Suggestions Needed

  • Thread starter Thread starter fgwiii
  • Start date Start date
F

fgwiii

Hello,

I am using Access97 and I currently have a db that I use to record the pages
I receive from remote sites but I am currently using a combination of check
boxes for the pages and text fields to record subpages received.
i.e. pages 1,2,3,4,5,6,7,8,9,12 Subpages 10-01, 10-02, 11-01 ,11-02, 13-01,
13-02, 13-03.

Each individual record from a site may receive at least the about of pages
as shown in the example above.

I am looking for another way to record this information that will allow me
to create reports that will provide counts of the pages and the subpages that
have been received, as well as producing a log (report) for each site's pages.


Thanks

Fred
 
Hello,

I am using Access97 and I currently have a db that I use to record the pages
I receive from remote sites but I am currently using a combination of check
boxes for the pages and text fields to record subpages received.
i.e. pages 1,2,3,4,5,6,7,8,9,12 Subpages 10-01, 10-02, 11-01 ,11-02, 13-01,
13-02, 13-03.
Click to expand...

owwwww.......

That's human readable... barely. It is NOT correct from a database design
standpoint!! What do you mean about the "checkboxes for the pages"? What is
the actual structure of your table?
Each individual record from a site may receive at least the about of pages
as shown in the example above.

I am looking for another way to record this information that will allow me
to create reports that will provide counts of the pages and the subpages that
have been received, as well as producing a log (report) for each site's pages.
Click to expand...

You really, really need to normalize your table structure. Rather than a big
opaque text string listing page numbers, you need *another table* with one
record per page or subpage received. I don't know what these are "pages" of,
or how pages relate to subpages... but you will really need two or perhaps
three related tables to properly manage this information.
 
I apologize. It is not a text string. I have about 20 or so fields utilizing
check boxes and some of those fields that may have sub pages, have a text box
next to them to record the sub page number.

While I do understand relational databases, and I have an idea of what I
want, I just lack the know-how to implement it!

At one point I created table "A" containing a list of available pages, and
then created another table "B" with multiple lookup fields all relating to
the field in table "A" with the list of available pages. My concern was would
this design produce the results I was looking for and would the database be
stable in the long run? Also, how would I get the page counts out that I need.

Now perhaps you understand why I am posting this message!

Thank you for your time.

Fred
 
I apologize. It is not a text string. I have about 20 or so fields utilizing
check boxes and some of those fields that may have sub pages, have a text box
next to them to record the sub page number.
Click to expand...
While I do understand relational databases, and I have an idea of what I
want, I just lack the know-how to implement it!

At one point I created table "A" containing a list of available pages, and
then created another table "B" with multiple lookup fields all relating to
the field in table "A" with the list of available pages. My concern was would
this design produce the results I was looking for and would the database be
stable in the long run? Also, how would I get the page counts out that I need.

Now perhaps you understand why I am posting this message!
Click to expand...

Well what I don't yet understand is the structure of your tables.

As far as Lookup Tables go please read:

http://www.mvps.org/access/lookupfields.htm

for a critique of what many of us consider a major misfeature in Access.

If you have multiple FIELDS in a table, that's almost as bad as storing
multiple values in one field. "Fields are expensive - records are cheap" is an
old saying around here!

Please step back a bit and describe the real-life entities that you're
modeling in this database. What is "a page"? A physical sheet of paper? a
webpage? a page number in a Word document? or what?

Then describe your Tables. What is the name of your table? What are the names
and datatypes of some relevant fields? What real-life Entity (person, thing or
event) is represented by a given record in the table?
 
On a daily basis I receive physical pages which are numbered 1 through 13.
Some of the pages like pages 10, 11, and 13 may have sub numbers indicating
that they are additional pages since they did not have enough room on the
initial page for all of the information to be recorded.

I need to verify that each page was received and produce a paper report that
will be forwarded to others within my company for tracking purposes.

My tables are Pages_Received, Subject_Positions, Page_list, site_id, and a
table that is linked to an Excel spread sheet that is updated daily.

In the Pages_Received table I have an Autonumber field for ID, Date fields
for date received and date sent, a field to indicate the color of the copies
received, a check box field for each page that was received and a memo field
to record issues that may arise.

Thanks

Fred
 
On a daily basis I receive physical pages which are numbered 1 through 13.
Some of the pages like pages 10, 11, and 13 may have sub numbers indicating
that they are additional pages since they did not have enough room on the
initial page for all of the information to be recorded.

I need to verify that each page was received and produce a paper report that
will be forwarded to others within my company for tracking purposes.

My tables are Pages_Received, Subject_Positions, Page_list, site_id, and a
table that is linked to an Excel spread sheet that is updated daily.

In the Pages_Received table I have an Autonumber field for ID, Date fields
for date received and date sent, a field to indicate the color of the copies
received, a check box field for each page that was received and a memo field
to record issues that may arise.
Click to expand...

The problem with your design is that if the Powers That Be ever decide that
there will be a page 14, or that they need a Page 10-03, you will need to add
a new *field* to your Pages_Received table; revise your form; revise all your
reports; revise all your queries.... OUCH!

I would suggest instead that rather than having *one* daily PagesReceived
record with 18 (or howver many) yes/no fields, that you have two tables:

Reciepts
ReceiptID <Autonumber primary key>
ReceivedDate
SentDate
Comments <Memo>

PagesReceived
ReceiptID Long Integer <foreign key to Receipts>
PageNo <1 to 13, currently, expansion allowed>
SubpageNo
Color <this could be in Receipts if all pages in a batch are the same color,
but I'd leave it here since color is an attribute of a page, not a batch>


You might want to have a little utilty table of all the (currently) valid page
numbers and subpage numbers so that you can use a combo box or append query to
more quickly enter the pages actually received.
 
Back
Top