help pulling the data i want.

[Daniel M]

I have a table that i am trying to get data from. problem is i dont know how
to get just the data i want.

I have a history table table that lists unit serial number, failure, and
replaced components. If a unit has multiple componet replacements i have
multiple entries.

IE:
serial 1234
failure battery not charging
replaced component battery

serial 1234
failure battery not charging
replaced component power connector

I would like to be able to pull the data into a table but ideally would like
to see it like this.

ie: serial 1234 / failure battery not charging / component battery, power
connector.

I dont want to repeat the serial and failure for the components replaced.
Each entry is time/date stamped and has a unique ID so i should be able to
filter on the same date along with serial, failure to get all the compenents
changed but i'm not sure how to get all this.

Any ideas? I can put the data in a report or in a simple table view like if
i ran a sql query. Thanks for any help.

 

[Tom van Stiphout]

On Wed, 10 Jun 2009 14:13:01 -0700, Daniel M

You have an incorrect database design. As you wrote yourself, for each
failure there can be several replacements. That means that you should
have at least two tables, with a one-to-many relation. Something like:
tblRepairs
RepairID autonumber PK
RepairDate
SerialNumber
ProblemDescription

tblRepairComponents
RepairID long int PK
ComponentID long int PK

tblComponents
ComponentID autonumber PK
ComponentName

Access is not Excel on steroids. You really need to design a
relational database; then other things like "getting the data I want"
will fall into place much more easily.

-Tom.
Microsoft Access MVP