Expression too long in Query. What get-arounds are there?

[martinbanks]

I have a order entry system that performs a long list of multiplications (most of which are n x 0) to result in quantities of items ordered. It is long because each item applies to 10 possible variations of a product.

To clarify a little here's an example; An 'Ashley seat' is the same across the range, needing 1 per 'chair', 2 per '2seater' and 3 per '3seater'. The other 7 variations on this product are blank, so it multiplies by 0 for those. The expression for this worked fine, but now needs to expand and allow the option of 'Complete', 'Trim only' or 'Seats only'.

So the formula is:
**edit: I've put in a load of carraige returns to make the formula a bit clearer to read.**

Qty: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QJtyPer],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA]+
[Forms]![frmChooseAProductForOrder]![BOrders]*[tblMainTable]![QtyPerB]+
[Forms]![frmChooseAProductForOrder]![COrders]*[tblMainTable]![QtyPerC]+
[Forms]![frmChooseAProductForOrder]![DOrders]*[tblMainTable]![QtyPerD]+
[Forms]![frmChooseAProductForOrder]![EOrders]*[tblMainTable]![QtyPerE]+
[Forms]![frmChooseAProductForOrder]![FOrders]*[tblMainTable]![QtyPerF]+
[Forms]![frmChooseAProductForOrder]![GOrders]*[tblMainTable]![QtyPerG]+
[Forms]![frmChooseAProductForOrder]![HOrders]*[tblMainTable]![QtyPerH]+
[Forms]![frmChooseAProductForOrder]![IOrders]*[tblMainTable]![QtyPerI]+
[Forms]![frmChooseAProductForOrder]![JOrders]*[tblMainTable]![QtyPerJ],0),0))),0))

But it is way too long. I tried changing everything that gets referenced to shorter names (tMT --> tblMainTable for example) but this not only isn't ideal but also was still too long!

Is there a different approach I can use for this? Especially as it will also need to do the same expression with 2, 3 etc after all the [nOrders] and [opgComplete]s for the other products on the same order because it runs 9 separate Append Queries, one for each product on an order, to append to tblOrders and then run a select query which collates everything with the Order Number that was assigned to this order.

PS Let me know if anything isn't clear.

PPS I believe that longer expressions can be done in VBA but I don't know where I put this code and how it becomes 'attached' to my forms/queries/whatever.

[LillMcGill]

get-arounds are there?

Normalize your tables. Unless I am misunderstanding, you have fields named QtyPerA, QtyPerB, QtyPerC, QtyPerD, etc.

[martinbanks]

Normalize your tables. Unless I am misunderstanding, you have fields named QtyPerA, QtyPerB, QtyPerC, QtyPerD, etc.

-A to J are the 10 variations each product can have, which can be different everytime (generally Chair, 2 seater and 3 seater, but can be up to 10 different ones), so I use A-J to represent them but in practice it looks up what these are from tblProductsTable.

-Each product is made up of multiple items. The same items can sometimes apply to all variations or be specific to one of them (for example Arms are generally the same across the products variations, whereas 'Bottom Strips' will all have different lengths. So each variation has a quantity for each item.

So is there a way could I normalize them?

[Rawb]

I agree with . . . Nancy on this one. Without seeing the entire layout of tblMainTable, I can't be sure, but it looks like you're treating your Tables as spreadsheets instead of Tables.

That's actually a pretty common mistake, but it can lead to situations like this where calculations can get out of hand (as well as increasing the chance of orphaned records, bloat, and database corruption).

While I strongly suggest redesigning your Tables, I know that sometimes that's not a feasible solution (aka "administrative interference," or "my boss says I can't"). If this is the case for you, I'd recommend breaking it down to it's components using a Query. For example, you could have a Query that is composed of 10 calculated fields, each one like so:

QtyA: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA],0),0))),0))

QtyB: IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=1,
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerB],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=2,IIf([tblMainTable]![S/T?]="Trim",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerB],
IIf([Forms]![frmChooseAProductForOrder]![opgComplete]=3,IIf([tblMainTable]![S/T?]="Seats",
[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerB],0),0))),0))

etc. . .

Then you can just turn your calculation into:

QtyA+QtyB. . .

[martinbanks]

I agree with . . . Nancy on this one. Without seeing the entire layout of tblMainTable, I can't be sure, but it looks like you're treating your Tables as spreadsheets instead of Tables.

That's actually a pretty common mistake, but it can lead to situations like this where calculations can get out of hand (as well as increasing the chance of orphaned records, bloat, and database corruption).

While I strongly suggest redesigning your Tables, I know that sometimes that's not a feasible solution (aka "administrative interference," or "my boss says I can't"). If this is the case for you, I'd recommend breaking it down to it's components using a Query. For example, you could have a Query that is composed of 10 calculated fields, each one like so:

Thanks Rawb and Nancy, I am happy and free to alter absolutely anything in this database, there are no constraints. The only thing that matters is the end not the means.

What do you want me to show you from tblMainTable? I really can't see how it can be changed and/or isn't normalised as the 10 fields are all individual to the item. They are attributes of each item, like if it was a table of creatures and the A-J were things like Legs, Arms, Heads, Eyes. These would be different for many creatures.

I'm happy to change the structure if that is the way to go but where will I put the quantities? And what advantage will it be?

[Rawb]

Ideally, that information would be contained in separate Tables. Using your body parts analogy, you could have the following:

Table Bodies

BodyID|FullName
1|Rawb
2|martinbanks

Table BodyPartTypes

PartID|PartName
1|Head
2|Arms
3|Legs

Table BodyParts

BodyID|PartID|Qty
1|1|2
1|2|2
1|3|2
2|1|2
2|2|2
2|3|1

It takes a little bit of cross-referencing, but the above BodyParts Table effectively becomes:

Table ExpandedBodyParts

FullName|PartName|Qty
Rawb|Head|2
Rawb|Arms|2
Rawb|Legs|2
martinbanks|Head|1
martinbanks|Arms|2
martinbanks|Legs|2

This allows you to save a (potentially) enormous amount of space because you're just storing the IDs and not Text Strings. In addition, you're not limiting yourself to just a certain number of items. You can have as many (or as few) as necessary.

Without knowing your current Table layout however, I can't tell you how you should change yours.

P.S.
That's right folks! I'm an Ogre Mage from Warcraft, I've got 2 heads!

[LillMcGill]

Good-looking tables you have there, ya ogre mage.

[martinbanks]

I'm really really sorry Rawb, but I am really lost as to what I need to that for. Maybe it's because I'm really an Excel boy and I've just made the switch. What I don't see the advantage of is creating a whole new table and then repeating information on every single item that at present is all on one line.

I think you are all telling me that if it is in a different table I will be able to then do some kind of expression whereby I'd just multiply the AmountOrdered by the sum of all the quantities in this new table that have the item ID in question. Is this correct? And would it be a lookup expression?

[martinbanks]

Also Rawb, I used your formulas as above but it was giving me the same wrong result as I was getting before. I also came up with a new idea: QtyA: (IIf([Forms]![frmChooseAProductForOrder]![opgComplete]<3,IIf([tblMainTable]![S/T?]="Trim",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA],0),0))+(IIf([Forms]![frmChooseAProductForOrder]![opgComplete]<>2,IIf([tblMainTable]![S/T?]="Seats",[Forms]![frmChooseAProductForOrder]![AOrders]*[tblMainTable]![QtyPerA],0),0))

Which gives the correct results in a select query (because it doesn't seem to allow me to have extra fields in my append query, when I close it an re-open it they have disappeared) and then in my append query I am asking it to add them up. But rather than add them up it lists them numerous times for each item with the same quantities repeating for them all.

I'm sooooo lost on this

[Rawb]

In many (smaller) cases, your "spreadsheet" approach to Tables will work. However, for professional level databases as well as ones that could become very large, that's a very poor way to set things up.

Normalization can be a fairly difficult concept to understand, but all it really means is "Design your database to be as resistant to inconsistencies and corruption as possible."

For a pretty good explanation of normalization, check out this link: http://en.wikipedia.org/wiki/Database_normalization

[martinbanks]

Ok, I'm on the brink of losing my mind (and job) on this now, because what should clearly (to me) be working, isn't. I am getting ridiculous, unexplainable problems with my queries whereby it returns different results depending on what other products are on the form, even though each query looks at different boxes in the form, the only universal one being the customer and the order number.

So... I have started again from scratch, doing my tables the way you suggest. What I don't understand though is how to set the relationships up.

I've attached a picture of how it's set up so far but I don't get how this Quantities Per Variation should work (so it is probly wrong on the pic)

- Each Product has multiple Items. (eg a settee has seats, arms, platforms etc)
- Each Product has multiple Variations. (eg a settee can be 2seater, 3 seater etc)
- Each Product will have a different set of Variations to the next. (eg one settee might have a chair, 2seater, 2½seater and 3seater, where another has a 2seater, 3seater, bed and corner unit)
- Each Item needs a Quantity in each of those Variations. (eg a 2seater has 2 seats, 2 arms, 1 platform; a 3 seater has 3 of those same seats, 2 of those same arms and 1 platform which is different to the 2seater, ie some Items are on more than one Variation, some are unique to one Variation and the quantity can differ)

So should the Products be related to these Variations or the Items?

Extra info: My old setup had 10 Variations on each Item (where it had less than 10 the extras were all 0s) and each Product stored the name of those Variations (where it had less than 10 the extras were all empty). The form updates when you choose a Product with the Variation names next to the text box for entering the quantity ordered, which it then multiplied by the quantity of each Item on that Variation.
It ran append queries for each product separately (the form displayed a possible 9 products for an order) then a select query collated all the items in the table it appended to that had the same order number.
Attachment 2218

[Rawb]

Sorry it's taken me so long to get back to you, but I ended up being pretty busy today.

I've looked at your Relationships (and, by extension, your Table Layout) and, although it's still not a fully normalized setup, I think it's something we can work with.

I do have a couple of questions though:

• What's the purpose of tblProducts? It seems like that's not doing anything except repeating information that's already going to be stored in tblItems/tblCustomers. If you can get away with it (for now at least), I'd say just delete tblProducts.
• Also, I'm not sure exactly how tblVariations (and tblVariationsList) work. I'd recommend making a new field in tblItems called VariationID and store that number in both tblItems and tblVariations (and do away with ItemID in tblVariations completely). Better yet, you could do away with the Variations Tables completely by making new Items for each variation (have an item for a 2seater settee and a completely separate item for a 3seater settee). That may not be possible though, depending on how it's used.

As always, back up your database before making any changes!