one query multiple tables

[splashout1]

Hi

I would like to set up a database where by there will be multiple
tables with different structures but all will contain a id key which
will be under the same feild name.

The reason for the multiple tables is that each one will hold data for
a certain job.

Items are produced which will contain this key on them.

the reason for the database is to enable someone with no knowledge of
the data to be able to search and compare the data with what has been
produced.

So my thinking is if I as the data controller enter the tables with a
job number as their name, and set up a querry to return all fields(*)
where the key(same filed name in every table) is equal to a bound txt
box on a form. As long as the user could select what table the querry
runs off by choosing the job number then they would have data available
to check against what was produced.

Is this possible in anyway or is there better thinking in how to
accomplish this.

Thanks in Advance

 

[Rick B]

Your data structure is not correct. You would have one or more tables to
store your records. Each job would be a separate record in your table.

If your table name or filed names include data (the job number, for example,
or a month, or a category, etc.) then you are probably not using proper
Relational Database normalization rules.

 

[splashout1]

Hi Rick

I know that and I would if i could however each jobs data can conatain
any number of fields which are completly unique to that client and or
job. so being able to normalize the data base is not realy an option.

The purpose of trying to get this to work is so that somone with no
computer knowledge at all can find the relevent record for that job.

The easiest way for that would be for someone to simply enter the job
number and unique key onto one form.

each product produced could contain any piece of data from the
corresponding record in that file and it is this that would need to be
checked from the record found.

If anyone has any ideas on how this could possibly work this would be
great.

Thanks
Again

 

[Amy Blankenship]

There's always a way to normalize the data. One way would be a table that
defines what types of data will be collected for each type of job. However,
this type of thing doesn't work well with Access forms, so you'd have to use
something else for your data entry unless you're really good or really
determined with Access forms.

Perhaps if you posted what the requirements are for the database to the
tablesdbdesign forum, you might find normalization is not as far beyond your
reach as you think.

HTH;

Amy

 

[splashout1]

Thanks for reading Rick.

The Data entry isnt an isue really as i will simply be putting the
files in manually not inputting any data the pure function of the
database will be to allow production guys to find and view a record of
data from the corresponding job.

How can you normalize something that is undefined and will always be
different.

I could have a master file that is simply a file with hundreds of
fields to accomidate any possability.

One of this things that in theory sounds so simple.

Thanks
Glyn

 

[Amy Blankenship]

You have something like a "Properties" table that defines each of the
properties of whatever the "Object" is, linked to the Object with an
ObjectProperties table. So the Properties table would be very thin
horizontally, as it would only contain a unique ID and a description field,
then possibly length or data type information. The ObjectProperties table
would only contain a foreign key to the object table and the Properties
table, and then possibly a PropertyOrder to control visual presentation of
the properties on the screen, Then you'd have an SituationPropertyValue
table that would tell you what the value is of each object is in each
instance (whatever the situation is that would have you creating a record in
your multitudinous tables you say you want). This would contain a
PropertyID, SituationID, and value, which is the value in that situation.
The situation definition would have its own table.

I think you'd find that difficult to enter by hand, just because there would
be such a trail of ID-only information you'd have to follow till you got to
the point where the information actually was. One step people nearly always
leave out when planning a data driven project is the design and production
of tools to get the data into the database. With a properly designed
database these are nearly always necessary.

You'll probably get a better and/or more detailed response by doing as I
suggested, posting to the tablesDBDesign forum with your specific
requirements. Hint: these need to be more detailed than "it's undefined and
will always be different."

One thing that sets a skilled developer apart is an ability to go from the
specific to the general and create a design that is as flexible as is
needed. More than likely there IS a general relationship possible that
doesn't require you to go as far as a generalized properties table.
However, you're only going to be able to harness the expertise of the people
who volunteer their time here if you actually provide specifics for them to
generalize from. They shouldn't have to dig it out of you, and many have
gotten jaded enough that if they have to ask you for more specifics they'll
skip your post.

To that end, don't expect that I'll provide any more feedback to this
thread, since you're posting the wrong question to the wrong forum.

Hope this helps;

Amy