Help with database/table design

[ragsman]

Hi,
I am trying to set up simple trouble report type database that will be based
on documents with ID's like such:
0-SO-1-1, or 1-EA-68-9. There are multiple documents, and each document may
(but don't necessarily) duplicate with the exception of the first numeral (0,
1, or 2 only). So, there may exist a 0-SO-1-1, 1-SO-1-1, and 2-SO-1-1, or
only 1 or 2 of these. There are probably 400 or so documents total.

I can either store all of the document records in 1 table with fields for
each value (0,SO,1,1 for example) or store the records in 3 tables, one each
for 0, 1, and 2, OR let the primary key be the readable value (i.e. 0-SO-1-1)
in 1 or 3 tables.

The other table is the trouble report table, which will hold all of the
problem data and relates to the specific document (many troubles to one
document) using the document primary key.

One of the big concerns is designing the reports. They will usually come from
the trouble report table, but i'll have to always combine the values from the
document table instead of using the document table primary key for it to be
readable, unless I use the combined procedure (0-SO-1-1) as the primary key.
Or, make a separate field in the trouble report table (or the document table)
that holds the combined fields value to use for readability. I don't know
the best way to do it!

Wow, that's harder to explain than I anticipated... Please ask for
clarification if you need it.

Thanks for your help!

Ragsman

 

[Guest]

I would suggest that your document table uses an autonumber field for the
primary key, calling that DocumentID. Then set up another field, called
DocumentName, which contains the actual name that you will use in reports and
forms. Your trouble report table will then tie in with the documents through
the DocumentID. However, your forms and reports will simply use the
DocumentID to reference over to DocumentName, in a one-to-many relationship.

 

[ragsman]

thank you for the reply

I have that relationship set already,. but if I only store DocumentID
(autonumber) in the trouble report table, how can I retrieve the associated
DocumentName from the document table in reports based on the trouble report
table?

I would suggest that your document table uses an autonumber field for the
primary key, calling that DocumentID. Then set up another field, called
DocumentName, which contains the actual name that you will use in reports and
forms. Your trouble report table will then tie in with the documents through
the DocumentID. However, your forms and reports will simply use the
DocumentID to reference over to DocumentName, in a one-to-many relationship.

Hi,
I am trying to set up simple trouble report type database that will be based

[quoted text clipped - 27 lines]

Ragsman

 

[Guest]

Create a query that combines information from the trouble report table, with
the DocumentName from the document table. Then base your report on that
query. The database will associate the DocumentID from the trouble report
table with the appropriate DocumentName. That is the whole point of using a
relational database, because it will relate the correct data together.

You can test this by doing a simple query and report on just DocumentID and
DocumentName from the two different tables. You will see that the database
does indeed know which document name goes with the appropriate document ID.

thank you for the reply

I have that relationship set already,. but if I only store DocumentID
(autonumber) in the trouble report table, how can I retrieve the associated
DocumentName from the document table in reports based on the trouble report
table?

I would suggest that your document table uses an autonumber field for the
primary key, calling that DocumentID. Then set up another field, called
DocumentName, which contains the actual name that you will use in reports and
forms. Your trouble report table will then tie in with the documents through
the DocumentID. However, your forms and reports will simply use the
DocumentID to reference over to DocumentName, in a one-to-many relationship.

Hi,
I am trying to set up simple trouble report type database that will be based

[quoted text clipped - 27 lines]

Ragsman