Help with WHERE clause in query selecting from queries...

[Adam Clauss]

Alright, I've got a table setup that indicates individual purchase orders.
Each purchase order can contain up to 10 line items, so it was decided (long
ago) that the table would have separate columns (item1, item2, ..., item10).
Now, I know this is a horrible way to do it, but this cannot be changed at
this point.

Now, each item has an object code associated with it (object_code1, ...,
object_code10). I now need to perform a summary operation based on grouping
by object codes. So I setup a union query called AllItems that simply
UNION'd the 10 sets of columns together to generate one list of all the
items combined. This query outputs three columns:
account number (that the PO the item came from was for)
object code (the object code associated with that item)
price (the cost of that item)

Good so far?

I also have a table that contains a list of all the object codes (they are
numbers -- stored as strings for use elsewhere) and their descriptions.
So, I created a query ("Summary") which is the following:

SELECT ObjectCodeStrings.object_code_str, ObjectCodeStrings.description,
Sum(AllItems.price) AS total
FROM AllItems INNER JOIN ObjectCodeStrings ON AllItems.object_code =
ObjectCodeStrings.object_code_str
WHERE AllItems.account_number=[Account number:]
GROUP BY ObjectCodeStrings.object_code_str, ObjectCodeStrings.description,
Val([object_code_str]), AllItems.account_number
ORDER BY Val([object_code_str]);

Now, if this query is run WITHOUT the WHERE clause limiting the account
number, it seems to run correctly. I get a list of items, along with valid
object codes and object code descriptions.

However... if I add in that WHERE clause, all of a sudden all my
descriptions show up as the special "box" character that typically indicates
a newline or tab...
That character does not exist in my object code table, so I'm not sure where
that is coming from. Note that the actual object_code_str values are
correct - its just the descriptions that come up incorrect.
Unfortunately... I NEED those descriptions...

Any clues as to why it would do that?

 

[John Vinson]

Alright, I've got a table setup that indicates individual purchase orders.
Each purchase order can contain up to 10 line items, so it was decided (long
ago) that the table would have separate columns (item1, item2, ..., item10).
Now, I know this is a horrible way to do it, but this cannot be changed at
this point.

:-{( Ok I'll spare you the normalization rant...

Now, each item has an object code associated with it (object_code1, ...,
object_code10). I now need to perform a summary operation based on grouping
by object codes. So I setup a union query called AllItems that simply
UNION'd the 10 sets of columns together to generate one list of all the
items combined. This query outputs three columns:
account number (that the PO the item came from was for)
object code (the object code associated with that item)
price (the cost of that item)

Good so far?

Very good. A normalizing union query! Any chance you could base a

I also have a table that contains a list of all the object codes (they are
numbers -- stored as strings for use elsewhere) and their descriptions.
So, I created a query ("Summary") which is the following:

SELECT ObjectCodeStrings.object_code_str, ObjectCodeStrings.description,
Sum(AllItems.price) AS total
FROM AllItems INNER JOIN ObjectCodeStrings ON AllItems.object_code =
ObjectCodeStrings.object_code_str
WHERE AllItems.account_number=[Account number:]
GROUP BY ObjectCodeStrings.object_code_str, ObjectCodeStrings.description,
Val([object_code_str]), AllItems.account_number
ORDER BY Val([object_code_str]);

Now, if this query is run WITHOUT the WHERE clause limiting the account
number, it seems to run correctly. I get a list of items, along with valid
object codes and object code descriptions.

However... if I add in that WHERE clause, all of a sudden all my
descriptions show up as the special "box" character that typically indicates
a newline or tab...

Is the Description a Memo field? If so, that's the problem: a Memo
passed through a UNION query gets truncated to 255 bytes, and at least
in some versions of Access, can run into problems like this due to
Unicode compression.

Try changing the UNION operators to UNION ALL to keep Access from
trying to deduplicate the descriptions; this *should* avoid the
problem.

John W. Vinson[MVP]

 

[Adam Clauss]

:-{( Ok I'll spare you the normalization rant...

Thanks

Very good. A normalizing union query! Any chance you could base a
MakeTable or Append query upon it to "change it at this point"? <g>

Unfortunately, its not the tables themselves, but all the code we have based
around it at this point...

Is the Description a Memo field? If so, that's the problem: a Memo
passed through a UNION query gets truncated to 255 bytes, and at least
in some versions of Access, can run into problems like this due to
Unicode compression.

Try changing the UNION operators to UNION ALL to keep Access from
trying to deduplicate the descriptions; this *should* avoid the
problem.

Alright, I'll give that a try tomorrow when I go in. (I actually meant for
it to be a UNION ALL, I just noticed that the descriptions weren't even
there and got sidetracked

Thanks for the suggestion!