Table & Relationships questions

[indyaries]

Greetings,

Have questions on Table design and Relationships. Using Access 97 SR1.

I have Time & Billing info that I'm working with. Every 2 weeks, I receive a new batch of data. It arrives as an Excel spreadsheet, which I convert to CSV.

I then import into Access using an Import Spec. There are 1400-1600 records per data batch.

There are 23 fields in the data, which includes such fields as:
BUserID = Time & Billing ID (ex: BIO00197)
PERN = Employee ID (ex: E00005121)
EmplName = Employee Name

BUserID and PERN are unique to each employee. Each employee has several records in each data batch.

Part of what I'm doing is checking each line for errors. I have numerous queries that check for different types of errors. Each line can have multiple errors.

I though that perhaps I could create a new table called t_Errors_and_Info. This would be associated with the Empl and their records. Thinking that I could use an Append Query that would append the type of error found to the Errors and Info table, but not quite sure how to do this, or if it's even the best method.

When I run the multitude of Reports I've been tasked to develop, the Brass wants to see the Employee, the detail line containing Time & Billing information, and any errors found.

Was thinking along these lines for table setup:

Table 1 = t_EmplInfo / This would be extracted using a Distinct Query against the main table, then using a Make-Table query to create the new EmplInfo table.
Fields
RecID
BUserID
PERN
EmplName
ORG

Table 2 = t_eBizLines
Fields
These would have all of the remaining Time and Billing fields. Wondering how best to relate this to the EmplInfo table.

Table 3 = t_Errors_and_Info / Need to relate this to the table t_eBizLines
Fields
ErrorTypes
Info

I apologize for such a lenghtly post. Thanking one and all for any assistance you may provide.

Regards,

Bob in Indy

 

[lameid]

I'd append all the records to a table with an autonumber unique index. Then I'd have my errors relate to this autonumber field (after all the alleged keys could have errors). Also for the sake of making things auditable I'd add either a created filed or filename field to your table so you know what was imported.

 

[johnnymac43]

Following on from lameid's suggestion, you would need to report separately any records with an invalid employee ID, and then link the rest as follows:

> Table 1 has a 1..M relationship to table 2 on employee ID.
> Table 2 has a 1..M relationship to table 3 on the autonumber field in table 2.

HTH

John

 

[lameid]

One more thing SR1?
For the sake of sanity get SR2!

 

[indyaries]

<quote>&quot;One more thing SR1? For the sake of sanity get SR2!<endquote>

I work for Uncle Sam. Sadly, I have no control over anything concerning my PC nor the software.

Access 97 SR1 &quot;seems&quot; to work fine. What advantage does SR2 have?

Thanks,

Bob in Indy

 

[lameid]

SR2 has patches in it that SR1 doesn't. SR2 has been out so long (before Office 2000) that I forget what was fixed in it. But I know there were definitely updates to Access.

Check out

http://support.microsoft.com/default.aspx?scid=kb;EN-US;151020

(MS KB article 151020)