Make table query question

[5ybarite]

I'm trying to put together a databse for a project @ work. I have a table for each piece of equipment I need to track numbers for. In addition, from time to time each piece of equipment gets service performed on it, so originally I thought it would be a good idea to store records of past service, and tentative future service in there as well.

I find now that I would like to query that information; but as that information is not related I received a table with weird output, some of which seemed made up. It could be that in attempting to use a make table query, I've done something very wrong.

My question though is this: Am I going about this the right way? Should I be using a make table query for this sort of thing? or should there be a many to many relationship table somewhere? Or should I query each table for the info I want, and then query those queries for the required info?

Any help/advice would be greatly appreciated!

Thanks,

~5ybarite

 

[LittleSmudge]

Various things you say worry me a lot.

I have a table for each piece of equipment

So what happens when you add another peiece of equipment ?
You add another table ?

I'd expect to see an Equipment table with one RECORD for each piece of kit.

I'd then have a 'Numbers' table that records each of the sets of 'numbers'. Add an EquipmentRef foreign key field to the Numbers table to link these two tables together in a one to many relationship.

I'd then have a Service table.
Again include an EquipmentRef foreign key to link Equipment to Service in a one to many way.

In the Service table I'd then have a ServicePlanned field to record the scheduled date for the service activity and a ServiceDate field to record the ACTUAL date of the service.
The difference between the two could then be used as some management effeciency measure on how prompt the service actually happens !


Is any of this making sense ?

I would like to query that information; but as that information is not related I received a table with weird output

All of the fields in a table must be related to each other. That's the fundimental point of Relational database theory.
If they are not related then they belong in a different table.

If you have non-relational fields in your table then you'll never develop a reliable database.

should there be a many to many relationship table

What's one of those ?
Do you mean a linking table that creates a many to many relationship ?
I don't see a need yet.
( Unless on service event needs to relate to multiple pieces of equipment. )

should I query each table for the info I want, and then query those queries for the required info?

err !
That way madness lies !


Fill in the gaps for us and we'll progress from there.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[dhookom]

We don't have too much information about your tables other than you seem to have a table for each piece of equipment. This is generally not a good idea.

If you have a specific need, please provide some sample records with field and table names. Also provide any desired output/display.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[5ybarite]

While I do understand what you both are getting at, 2 things remain true about this database:

1. The size, type, and shape of data I am tracking is completely different for each piece of equipment. For instance; we have 5 environmental air handlers, with data captured in 1 table tracking things like Humidity, and temperature, etc... and then a generator in another table, monitoring things like fuel level and frequency of battery changes. Like I said, even the type of data remains variable, where for some machines it might be a number, in others, I'm tracking alerts and therefore a significant amount of text.

2. As for adding new equipment, this is done with such infrequency that I have experienced it once in 4.5 years of working here. If we were to add, it would be to support the existing infrastructure (e.g. add an additional air handler, or power distribution unit requiring the requisite update to my Air Handler supporting table containing all possible valid entries therein (i.e. Air Handler 1,...Air Handler 5)

With all that being said, the one attribute common to all machines is that they will at some point be serviced, and there will be notes accompanying said service requiring a memo field in addition to a date field for the service performed.

The layout for each piece of equipment then is as follows: Autonumber (tertiary key) Date of the entry (tertiary key) & Name of equipment (if there is more than 1 of the same type i.e. Air Handler 1,2,3... potential tertiary key). All of this follow by the specific measurements performed on each piece of equipment and then fields for service. specifically, date which I suppose could be tied in to the initial date field, and a notes section which is a memo field for any unusual happenings or reminders going forward.

Ultimately I don't have a specific goal for this data other than for it to be as useful as possible. I created all of this to interact with Excel and Word making the reports that I generate from this information go much more quickly and smoothly than it had in the past. But, I find that the farther along I come in the creation of this database, the more my Boss wants me to do with it.

Anyway, hopefully that is enough information and things are more clear. Thanks for your responses!



Thanks,

~5ybarite