Need a query with more than 32 tables, combine 2 queries?

[Heatshiver]

I have at least one form and one report that require more than the 32 table limit for a query.

I'm thinking that I can create two queries and somehow combine them... Can I do this with SQL?

Is there a better way?

 

[plog]

If CBS had a show about my uncanny ability to look through questions on here and see the true problem, it would be called, 'The Data Normalizer'.

My gut is telling me your data isn't properly structured. Why so many tables? Can you provide some table names and fields? Maybe a screenshot of what you have of your query in design view.

 

[Heatshiver]

Thanks for the response.

What's funny is that this is a problem because of normalization (at least as I've done it). I did this backwards for reasons, but I originally had one large table and began work that way. I then began normalization and came out to about 40+ tables as there is a lot of info for this database. I then had to reconnect all my fields, and this is when the issue occurred.

I followed the rules in this excellent article: http://www.utteraccess.com/forum/Normalizing-Database-t343208.html

I do have a few tables with actually one true single item, but using the 4 rules from that article they seem as though they need a separate table.

I've provided a few screenshots that may help out.

 

[Sketchin]

Uh oh...there are some major issues here. Starting with the fact that you have a table for every single piece of equipment. You should have just one equipment table, and separate them by part #, or something along those lines. Even if the equipment is unique, such as a shale shaker and a SSmodel, you could just have a separate category for each one if you need to differentiate between equipment types.

 

[boblarson]

Yeah, I was going to comment that you think it is normalized, but it is far from it. Repeating tables/fields is not normalized and, in fact, if you see something that looks like yours, it jumps out as not being normalized. Pat and Sketchin's suggestions are spot on.

 

[GinaWhipp]

Ummm, okay, yep there is a problem with the normalization... you went a step too far. Let's take...

tblSSModel1 and numbered ones

There should be one table

tblSSModel

The should all be listed there. THEN if you want specific values under specific combo boxes...

tblSSModel
ssmSSModel
ssmChoice <--- Here is where you put 1, 2, 3, etc...

On your report use the Combo Box and set the ControlSource as...

cboMyComboBox.Column(1)

...thereby eliminating the need for tblSSModel to be in the query at all.

 

[Heatshiver]

Thanks for the fast replies.

@Sketchin - So I should put (for example) all the Shale Shakers in one table and have a number attached to each of their components to individualize them? Or should I have all the equipment like (for example) all the Shale Shakers and Centrifuges together in one table with an attached number to individualize them?

@Pat - My thinking for the 8 Shale Shakers was that it is the highest possible amount they could have, but they can have less. And likely each Shale Shaker will be the same model, but the output values will be different.

 

[plog]

Wow, my data noramlization sniffing ability triumphs again. Your structure is not normalized. Here's a backhanded compliment, but I do mean it as a compliment on your initiative: it looks like someone who thoroughly read about normalization and then went overboard with it. Again, that's a good thing in my book--you took the time to learn, you took the time to apply what you thought was right, its just that you overdid it.

Tables and fields that have numerated names (i.e. tblCmode1, tblCmode2, etc.) are red flags of this. As are tables with the exact same fields. Removing all tables that end with a number greater than 1 eliminates 20 tables.

Additionally, you have tables that really don't do anything. tblUserID and tblProjectID each have 2 fields in them, both ID fields of the same object. I can't tell what tables they are linked to, but I can't imagine what they are doing.

Those are the big things, my gut, which has a pretty good track record so far, sees other issues-tblDate might be unnecessary and I think you are storing calculated data.

I'd clean up the numerated tables and then see if you can move fields into those tables without many (tblUsers, tblProjects). How come there isn't a project name or user name or anything else in those tables? Where is it?

 

[Heatshiver]

@plog - I did feel at times I was going overboard with the normalization, but I was more afraid of going under-board!

So I should combine all the tables that have numbers appended? I assume I would then just have the appended numbers within the table for individualizing?

I initially had UserID and Date in the same table, but I couldn't get a verification (to see if they had been used together) in VBA to work until I had them in their own tables with Primary Keys. Obviously, I wasn't following the Autonumber rule at the time, but I thought keeping them separate would still be a good idea.
The projectID and UserID were meant to be unique identifiers of what and who, but then I should now assume they should be in the same table?

I had cleared the data while doing my "normalization", so there were no values when I sent the screenshots.

Thanks you so much for the help. I know this a huge step in creating a database, and I just want to do it right.

 

[plog]

So I should combine all the tables that have numbers appended?

Most likely. Others have suggested specifically how to do it and they are correct. However, I'd first make sure you need them at all. Why would data go into tblCmode1 and not tblCmode2? What do those numbers signify?

The projectID and UserID were meant to be unique identifiers of what and who, but then I should now assume they should be in the same table?

I think you misunderstood a little. My point was that both those tables suffered from the same issue--no real fields in them. I'm not certain those tables should be merged--most likely they shouldn't. I was wondering why they each didn't have more fields. Also, hoow come each of them have 2 id fields in them? What differentiates one id field in those tables from the other id field in the same table?

 

[Heatshiver]

The reason I have multiples is that there are that many Shale Shakers possible, and each one can output different values under the same field name. I'm not sure if I'm explaining that right, but please tell me if not and I'll try to reword it.

The reason the tables didn't have more fields could be my own fault in terms of going overboard. Likely projectID could be part of another table, but maybe my thoughts at the time were that cross-checking in VBA had seemed to work when I had the field in different tables, which could still be my own fault.

I have two ID fields because I was stupid and instead of just appending PK or FK, I appended as IDPK or IDFK.

Thanks again! This will really help me.

 

[boblarson]

The reason I have multiples is that there are that many Shale Shakers possible, and each one can output different values under the same field name. I'm not sure if I'm explaining that right, but please tell me if not and I'll try to reword it.

Oh, you're explaining it quite sufficiently. But you are not seeming to understand that you only need ONE of those tables. You don't need one for each of the Shakers. You only need ONE Shale Shaker table.

So, in order to understand your data a little better let me ask a few questions.

1. The Shale Shaker table is storing information about a particular piece of machinery, correct?

2. The Centrifuge is another piece of equipment, correct?

3. SolidsCtrlSum is the table where you are wanting the output of the each Shale Shaker for a certain shift, correct?

4. To keep from having to write so many questions how about a brief explanation from you about each of your current tables and what it is you were planning on storing there.

Then we can make some suggested changes to your tables and relationships.

 

[Heatshiver]

I think the other posters were explaining that I should place all the Shale Shakers, or other pieces of equipment into one table, this I do understand and am quite glad to hear.

The SolidsControlSum table was created to link to all of the necessary tables for a solids control summary. There are other summaries that share the same information.

The main purpose of this database is for daily reports. There are about 8-10 different daily reports (a solids control summary being one of them), each sharing certain pieces of information needed in other daily reports. There are then many different tables for the specified information of each daily report. GenSum is a general summary table, which is needed in every daily report. On the other side of the spectrum are tables like HSE which is for comments, but not needed in every daily report. From there-which I now believe may be a mistake on my part-I tried to break down tables further. I think my confusion began when I look at the rules of that article and try to decide if any other record would use this information again, which would lead me to create another table.

Again, thanks for all the help. This a great learning experience for me.

 

[Sketchin]

You would want something like an "Equipment" table that would have fields like Description, serial number, category...etc. Then have a record for each peice of equipment.

 

[Heatshiver]

Again, thank you to everyone here for the help. I definitely needed this and appreciate all the replies.