Comparing Tables then making a new table

[Slurry Pumper]

Hi All,



I have a form that utilizes 1 master table to create several smaller tables with similar parsed data. There are many categories and these tables are each generated by both an add query then a delete query to filter data that is not wanted. These smaller tables are always either being created, appended to add data, or delete queried to remove data, but the data always comes from the same master table.

In the end, I use 1 table to produce around 16 other tables of data.

How can I now use the 16 tables to compare against the original table to produce a 17th table that contains all of the data that is not contained in the other 16 tables.

[ranman256]

create a UNION query that has all 16 tables, qnUnion16
select * from table1
union
select * from table2
union
select * from table3
etc.....

then make another query using OUTER join , to see what is not in table17.
select * from qnUnion16 , table17
outer join qnUnion16.field1 = table17.field1

BUT
if you are creating more and more tables (17??!!!) , then this does not seem a practical way of doing things.

[JoeM]

I have a form that utilizes 1 master table to create several smaller tables with similar parsed data.

I would question why you are doing that.
If it is because "there are multiple categories", just add a "category" field to your table to differentiate them.

Usually, having multiple tables are structured the same way is an indication that they should really all be in the same table.
And the problems you are having might be further proof that they should all be in one table (when a task that should be simple becomes rather difficult, it is often a sign of bad design).

[Slurry Pumper]

This is a searching tool that looks at all the items in a particular facility and from the master list of around 50K items or so, this data base will produce several smaller lists that all have similar items so that we can send a subject matter expert to the location and determine if that item is repairable. So far we have identified around 16 categories. Well actually 8 categories but now instead of just repairing, we have OEM commercialization added to the list for each category which has a few more criteria for items added to the search to determine if that is a viable way to go. Every time I go to give this to the facilities, they come up with another category, and now I would like to know what is left over after all the smaller lists are generated.

[Slurry Pumper]

I would question why you are doing that.
If it is because "there are multiple categories", just add a "category" field to your table to differentiate them.

Usually, having multiple tables are structured the same way is an indication that they should really all be in the same table.
And the problems you are having might be further proof that they should all be in one table (when a task that should be simple becomes rather difficult, it is often a sign of bad design).

Hey I hear ya, and I really should not be the one creating this database. A mind is a terrible thing to develop on your own.

Anyways, the smaller list are easy for other people with even less knowledge about fancy thinking boxes than me to print out, and oddly the whole thing is working out pretty well. I would have bet heavily against it a month ago when my boss told me to learn Access and do this. So now, all I really want to do is capture the data that isn't making the smaller lists to make sure that there isn't more stuff that is needed.

[JoeM]

All you need is one table. Just add a category field to the table.
You can get it to the smaller category-specific listings through queries (to filter by specific categories) - no need to create more tables. As you see, it needlessly overcomplicates things.

[orange]

Slurry Pumper,

How about giving us an overview of the business process involved in simple English?
Also, if you gave us some records to show readers what you are dealing with, then you may get more focused responses.
I agree with Joe that some categorization would simplify things.

eg pseudo sql

Select * from yourTable
where category in ("Cat1","Cat6") and
Desc like "*" & some search term & "*"

[Slurry Pumper]

The business process is all about capturing stock items that are repairable. So things like electronics, valves, pumps, mechanical seals, electric motors, and some other categories that could be site specific. Then as a side line project, the capturing of repairable items closely follows a path to OEM Commercialization (knock offs or counterfeit for those who don't know), although the products are not marketed as counterfeit and they are usually an improvement over the original stock item. Think about modifications to a car as an analogy.

A typical manufacturing facility can contain over 100,000 individual maintenance stock items that are needed for the day to day operation of the facility.

As a start, there is already a single table list of items that has over 30 columns of information pertaining to each item, and yes they can use 1 table to parse out information to produce these separate list that are needed to help personnel in identification of repairable or OEM commercializable items.

The complaint is that there is only one table, and the current data manipulation tools being used are inadequate in speed and require a high degree of computer savyness for the average maintenance personnel to extract the information they need.

The search tool I made does produce several smaller tables with specific information about items in a category, and will print out a report of those tables in an easy manner. It is already done, in use, and people are happier with it than the original search tool.
The other day, I was wondering to myself about the items that do not make any category search but should qualify for being a repairable or OEM commercialized part. So this is where the idea of comparing the smaller tables against the original came to be.

As far as showing examples, we here are contracted by these facilities to perform certain tasks, and as such there are agreements in place that will not allow me to publish a clients current stock positions.

I do thank all on this forum who earlier help with the creation of this search tool, and now who are helping me with this "between the cracks" list that I am trying to produce.

[orange]

Thanks for the description.
I think there are some details/aspects of "capturing stock items that are repairable" that would help .

As I understand things, there is a facility with 100K+ parts/stock items.
There is some categorization done ( I think that's how you break out your multiple tables).
Some how users/maintenance personnel have to search the database for parts.
My guess is that they repair items that are deemed repairable, and replace parts/units with items from inventory--but that's my guess.
It would be helpful if you could describe the specifics.

Even some made-up names/parts would help with context.

Good luck.

[Slurry Pumper]

OK fair enough lets see if I can explain some of the items.

"Capturing stock items that are repairable", is the term used to utilize collected data about an item that based on some criteria about the item will place it in the "Repairable" category as a repairable item in the database. All items are either repairable or non repairable (to make things simple). For example, we might have a facility that uses several PLCs (Programmable Logic Controller) of the same type from Allen Bradley, and these PLCs pretty much have the same power supply units contained within them. The collected data allows us to search the data with criteria such as the cost of a new power supply. If the cost of the power supply is over a certain threshold, and we can find a vendor who can repair the item to "new" condition for a rate that is less than the cost of a new item, then that item is repairable and thus it becomes a "Captured stock items that are repairable". The reason we categorize the data is to collect all the candidates that are alike so that someone can go find the item and determine if it is indeed repairable. These people are experts, so they have a list that pertains to just what they are experts at. This helps so that we don't send the guy who knows about power supply units to look at mechanical seals.
Now lets determine that the power supply units are being used at an elevated rate, for this case we will say that we use 25 of these per year. Then we multiply the cost of the power supply with the amount used per year to get the annual cost for the item. The item cost $750 * 25 = $18750. At 18750 we may determine that this Allen Bradley power supply is a good candidate for OEM Commercialization. Which is to say, we will design our own power supply utilizing the Allen Bradley power supply performance parameters, while staying away from any patent infringement issues, to replicate the power supply unit. If we can do that and lower the over all cost of the power supply, the overall maintenance cost associated with the replacement of the power supplies, or increase the up time production of the facility, or a combination of all of the listed factors the project for the OEM commercialization will move forward.

Finally, we don't just have 1 facility, we support several facilities in several industries that have their own tables of stock items they need. As you can imagine, there is no standard method for creating these data bases and usually even within the same company, they will have separate databases for their stocked items, so all of the tables are different, in fact if is rare to see a table that is identical in terms of column headings and input data when comparing. My search tool allows for categorizing the data from different sources by searching the noun identifiers contain in the column heading so that the end result for the subject matter experts looks the same on my table report for every facility they visit.

In the past, we would allow our clients to produce a categorized list of items for our experts to look at, and typically what happens is they either can't produce the categorized data in a timely fashion, or they have a hard time making the data readable for our user, thus increasing the time of implementing the repairable or OEM commercialization projects.

[orange]

Where exactly does collected data come from?
We just need a simple example --just as you would tell an 8 yr old.

Something happens; then someone is notified; data is recorded to describe...; this data is later analyzed by...QAW to determine category;....
....; an expert in XXX evaluates the "part"; then....... etc. etc.

[Slurry Pumper]

What are you thinking about starting a competitive endeavor?

The collected data is generated by the OEMs which either produce a B.O.M. (Bill of Materials), or a list of critical spares if they are a "good" supplier. Other collected data is generated every time an item is ordered. Everything about the order including what the procurer was wearing that day can be collected. Things like price, discount, shipping requirements, warrantee, etc... Still other data is collected by reports that will categorize vendors for on time shipping %, accuracy of orders, and just about everything that other forum users are dealing with in their data bases can be added to the collected data table. If any tidbit of information is discovered about an item, it will get noted in a collected data table. Now I don't need all of the collected data to do my job, and I don't even understand half of the bean counter entries, probably because I am involved in the reliability engineering side of things and don't even care about half of their stuff they collect, and I'm sure they don't care about half of the stuff I need either, but it is all there in a table.

This data is used by several departments in an effort to know and control what is in the facility.

[orange]

What are you thinking about starting a competitive endeavor?

No. I'm trying to understand your requirement. And what is in scope.


These things are definitely related

A:

The complaint is that there is only one table, and the current data manipulation tools being used are inadequate in speed and require a high degree of computer savyness for the average maintenance personnel to extract the information they need.

B:

If any tidbit of information is discovered about an item, it will get noted in a collected data table.

C:

Now I don't need all of the collected data to do my job, and I don't even understand half of the bean counter entries, probably because I am involved in the reliability engineering side of things and don't even care about half of their stuff they collect, and I'm sure they don't care about half of the stuff I need either, but it is all there in a table.

It sounds a bit like my great-grandmother's approach to filing photos. It's in the shoe-box somewhere.

Does your organization have any business analysts or IT personnel?

Good luck with your project.

[Slurry Pumper]

Of course they have IT professionals, and they are working on streamlining the tools for us that we need, but in the meantime while they take a few years to get their stuff together, I still have results to achieve and also to help my people get their bonuses for saving the customer money with respect to their MRO cost. To that end, I encourage competition, and that means that if you need to get the information for other sources with different tools, then o for it. It keeps departments relevant and engaged, and also lets us pick winners and losers.

The its in a shoebox analogy is partly because we are working across many companies, that lets face it, can't get a hold of their maintenance, reliability, and operations. If they had a hold on it, then they wouldn't contract us out to fix it for them.

So all I really wanted to do is combine 2 or more table with the exact same column count, and the exact same categories, so I can then compare against a master table. Now it seems like I may be interviewing a new IT department head. I will experiment with the union and select stuff mentioned at the beginning of the thread, and then eventually, I will move into the real reason I volunteered to take on learning the Access stuff, and that is to move my football stats into a database so I can better predict the outcomes.