Query with my report; help needed.

T

Tim Fierro

Hello,

I have a report that had been working fine, but now is not. When I go to
the design view of the query, I get the error that the field(s) could not be
represented or that something has changed.

I have even went as far as recreating the query and here is what I have.

SELECT tblOrder.ordVendor, tblOrder.ordDate, tblOrderLine.ordlineBoatId,
tblOrderLine.ordlineStatus, tblOrderLine.ordlineOrdId,
tblOrderLine.ordlineId, tblOrderLine.ordlineQtyReceived,
tblVitem.vitemDescription, tblOrderLine.ordlinePrice, tblBoat.boatName,
tblVendor.vndNameShort, tblBoat.boatpmsSpecialOrders
FROM tblVendor INNER JOIN (tblBoat INNER JOIN (tblVitem INNER JOIN (tblOrder
INNER JOIN tblOrderLine ON tblOrder.ordId=tblOrderLine.ordlineOrdId) ON
tblVitem.vitemId=tblOrderLine.ordlineVitemId) ON
tblBoat.boatId=tblOrderLine.ordlineBoatId) ON
(tblVendor.vndId=tblOrder.ordVendor) AND
(tblVendor.vndId=tblVitem.vitemVendor)
WHERE (((tblOrderLine.ordlineBoatId)=1 Or (tblOrderLine.ordlineBoatId)=5 Or
(tblOrderLine.ordlineBoatId)=7 Or (tblOrderLine.ordlineBoatId)=17) AND
((tblOrderLine.ordlineStatus)="R" Or (tblOrderLine.ordlineStatus)="P" Or
(tblOrderLine.ordlineStatus)="C"))
ORDER BY tblOrder.ordVendor, tblOrder.ordDate, tblOrderLine.ordlineBoatId,
tblOrderLine.ordlineStatus, tblOrderLine.ordlineOrdId,
tblOrderLine.ordlineId, tblOrderLine.ordlineQtyReceived;


In the design view it looks identical (as far as I can tell) to the old
query that somehow does not work now.

What is happening is that it is taking a long time for this query to run
when it used to be pretty fast. Then a glaring problem is shown. I have 2
orderlines for Boat #17 yet only 1 of them shows up in the query. Nothing
obvious either on why it only picked up one.

Another odd issue is when I get to the report using this query; it 'looks'
like it is tallying up more than it should and/or grabbing duplicates. Not
sure, but I dont' want to go into the report aspect until I find out if
there is something wrong with my query.

Anyone familiar with the language see anything peculiar that is obvious to
all but me as a newb of a few months. :)

Tim
 
M

[MVP] S.Clark

You can simplify the WHERE clause with:

WHERE
(tblOrderLine.ordlineBoatId IN (1,5,7,17)
AND
(tblOrderLine.ordlineStatus IN ("R","P","C")

Does that help?
 
C

Chris2

Tim Fierro said:
Hello,

I have a report that had been working fine, but now is not. When I go to
the design view of the query, I get the error that the field(s) could not be
represented or that something has changed.


Tim,

Go back to the earliest backup copy where the query was known to be
working. Take a *copy* of the backup, run the query to prove it is
working. Open it up in SQL View, and then compare that working SQL
vs. the non-working SQL you have posted now for any differences.


Sincerely,

Chris O.
 
T

Tim Fierro

[MVP] S.Clark" wrote in message
You can simplify the WHERE clause with:

WHERE
(tblOrderLine.ordlineBoatId IN (1,5,7,17)
AND
(tblOrderLine.ordlineStatus IN ("R","P","C")

I had to go to this, then it worked;

WHERE (tblOrderLine.ordlineBoatId IN (1,5,7,17) AND
(tblOrderLine.ordlineStatus IN ("R","P","C")))
Does that help?

Steve, It still doesn't bring up a line that I know should be brought up.
The Where part of the query now looks cleaner.

Here is what I have;

SELECT tblOrder.ordVendor, tblOrder.ordDate, tblOrderLine.ordlineBoatId,
tblOrderLine.ordlineStatus, tblOrderLine.ordlineOrdId,
tblOrderLine.ordlineId, tblOrderLine.ordlineQtyReceived,
tblVitem.vitemDescription, tblOrderLine.ordlinePrice, tblBoat.boatName,
tblVendor.vndNameShort, tblBoat.boatpmsSpecialOrders
FROM (tblVendor INNER JOIN (tblOrder INNER JOIN (tblBoat INNER JOIN
tblOrderLine ON tblBoat.boatId=tblOrderLine.ordlineBoatId) ON
tblOrder.ordId=tblOrderLine.ordlineOrdId) ON
tblVendor.vndId=tblOrder.ordVendor) INNER JOIN tblVitem ON
(tblVendor.vndId=tblVitem.vitemVendor) AND
(tblVitem.vitemId=tblOrderLine.ordlineVitemId)
WHERE (tblOrderLine.ordlineBoatId IN (1,5,7,17) AND
(tblOrderLine.ordlineStatus IN ("R","P","C")))
ORDER BY tblOrder.ordVendor, tblOrder.ordDate, tblOrderLine.ordlineBoatId,
tblOrderLine.ordlineStatus, tblOrderLine.ordlineOrdId,
tblOrderLine.ordlineId, tblOrderLine.ordlineQtyReceived;

There is definately 2 Orderlines where the boat is 17, the status on both
are C; yet the query is only finding one of them. When I look at the Order
(or should I say the order lines for that Order), both lines look identical
in data. I mean other than the item number and description/price; the
status codes look fine.

This query looks for all order lines (purchase order line items) for
particular boats; then makes sure that the status codes for these purchased
items are either Received, Pending Payment, or Closed.

Tim
 
T

Tim Fierro

Go back to the earliest backup copy where the query was known to be
working. Take a *copy* of the backup, run the query to prove it is
working. Open it up in SQL View, and then compare that working SQL
vs. the non-working SQL you have posted now for any differences.

Chris, thanks for the tip. I can't prove that it works for this incident
since the database contents have changed since that backup was done. IE:
This order was placed recently and the backup wouldn't have the data from
the past couple of weeks.

However, the actually SQL query statement I copied/pasted to see if it
works. It has the same issue of only reporting 1 line out of an order
instead of both lines that are known and visually showing the data should be
collected by the query.

Tim
 
J

John Spencer

Is it possible that one of the table join field has a null value in it? If any
of them did, you wouldn't get that row returned since all your joins are INNER
JOINs.
 
T

Tim Fierro

Is it possible that one of the table join field has a null
value in it? If any of them did, you wouldn't get that
row returned since all your joins are INNER
JOINs.

John, I would have hoped that I didn't have any nulls, but, since I did
recently add a couple of new fields; maybe I didn't place a value in a
couple.

I will go and check the tables.

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top