Lookup Query / SQL Help

[Garry]

I am trying to write a query for a colleague to pull out some data into an
excel file.

He is using a database which logs enquiries / quotes issued. However under
the detail section of the quote someone hase been entering "As Above" in the
part number field. This looks fine on the form view, but when pulled out
into excel obviously is of no use.

The relevant parts of the table are as follows:

LineID QuoteNo Item Part
1 100 1 Apple
2 100 2 As Above
3 101 1 Banana
4 102 1 Orange
5 102 2 Grape
6 102 3 As Above
7 102 4 As Above

I am trying to write a query to find the correct Part value when As Above
has been entered.

The logic is quite simple: If the Part value for LineID is "As Above",
lookup the Part value for LineID-1, then LineID-2 etc until you find the a
Part Value which is not "As Above". This will then be the correct Part
value.

Can something like this be done in SQL, or is there another way of acheiving
this result?

Any ideas appreciated.
Thanks,
Garry.

 

[Michel Walsh]

SELECT a.LineID, LAST(a.QuoteNo), LAST(a.Item), LAST(b.Part)
FROM

(myTable As a

INNER JOIN
(SELECT * FROM myTable WHERE Part<>'As Above') AS b
ON a.LineID >= b.LineID
)

INNER JOIN
(SELECT * FROM myTable WHERE Part <> 'As Above') AS c
ON a.LineID >= c.LineID

GROUP BY a.LineID, b.LineID

HAVING b.LineID=MAX(c.LineID)


should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

In addition to Michel's sage help, here might be
a way to update (change "yurtable" to actual
name of table, plus test first on backup of data):

UPDATE yurtable AS M, yurtable AS P
SET M.Part=P.Part
WHERE
(M.Part="As Above")
AND
P.LineID=
(SELECT
Max(q.LineID)
FROM
yurtable AS q
WHERE
q.LineID<M.LineID
AND
q.Part<>"As Above")

 

[Garry]

Thanks for the suggestion Michel. I can see what you are doing, but I have
tried it in Access and it doesn't work.

The query runs (blue bar at the bottom to 100%), but no results are
returned. When I go back to the query I notice that Access has changed the
SQL statement as follows:

INNER JOIN [SELECT * FROM mytable WHERE Part<>'As Above']. AS b

Same change appears in the second join statement.

Sorry I am not very up on SQL. Have you any idea why this is happeneing?

Thanks.

 

[Michel Walsh]

Hi,


Gary Walter approach should work too... Jet has, sometimes, the tendency to
replace a sub-query, in the from clause, with the strange syntax you
mentioned... but that is not necessary a good sign. A workaround is to make
a query:

SELECT * FROM myTable WHERE Part <>'As Above'

save it, say, under the name q1, then


SELECT a.LineID, LAST(a.QuoteNo), LAST(a.Item), LAST(b.Part)
FROM

(myTable As a

INNER JOIN q1 AS b
ON a.LineID >= b.LineID
)

INNER JOIN q1 AS c
ON a.LineID >= c.LineID

GROUP BY a.LineID, b.LineID

HAVING b.LineID=MAX(c.LineID)


If you have no result, can you check if q1 returns some records? It may
happen you get a 'lookup' problem, I mean by that that you have numbers, as
real stuff, but formatted, through an autolookup, into some text. If so, the
test must be against the real 'number', not the formatted appearance, here,
'As Above'.


Hoping it may help,
Vanderghast, Access MVP

Thanks for the suggestion Michel. I can see what you are doing, but I have
tried it in Access and it doesn't work.

The query runs (blue bar at the bottom to 100%), but no results are
returned. When I go back to the query I notice that Access has changed the
SQL statement as follows:

INNER JOIN [SELECT * FROM mytable WHERE Part<>'As Above']. AS b

Same change appears in the second join statement.

Sorry I am not very up on SQL. Have you any idea why this is happeneing?

Thanks.

SELECT a.LineID, LAST(a.QuoteNo), LAST(a.Item), LAST(b.Part)
FROM

(myTable As a

INNER JOIN
(SELECT * FROM myTable WHERE Part<>'As Above') AS b
ON a.LineID >= b.LineID
)

INNER JOIN
(SELECT * FROM myTable WHERE Part <> 'As Above') AS c
ON a.LineID >= c.LineID

GROUP BY a.LineID, b.LineID

HAVING b.LineID=MAX(c.LineID)


should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Garry]

Many thanks Michel & Gary. Both approaches work. Great lesson.

Think I need to get an SQL book and do some learning.

Regards,
Garry.

Hi,


Gary Walter approach should work too... Jet has, sometimes, the tendency
to replace a sub-query, in the from clause, with the strange syntax you
mentioned... but that is not necessary a good sign. A workaround is to
make a query:

SELECT * FROM myTable WHERE Part <>'As Above'

save it, say, under the name q1, then


SELECT a.LineID, LAST(a.QuoteNo), LAST(a.Item), LAST(b.Part)
FROM

(myTable As a

INNER JOIN q1 AS b
ON a.LineID >= b.LineID
)

INNER JOIN q1 AS c
ON a.LineID >= c.LineID

GROUP BY a.LineID, b.LineID

HAVING b.LineID=MAX(c.LineID)


If you have no result, can you check if q1 returns some records? It may
happen you get a 'lookup' problem, I mean by that that you have numbers,
as real stuff, but formatted, through an autolookup, into some text. If
so, the test must be against the real 'number', not the formatted
appearance, here, 'As Above'.


Hoping it may help,
Vanderghast, Access MVP

Thanks for the suggestion Michel. I can see what you are doing, but I
have tried it in Access and it doesn't work.

The query runs (blue bar at the bottom to 100%), but no results are
returned. When I go back to the query I notice that Access has changed
the SQL statement as follows:

INNER JOIN [SELECT * FROM mytable WHERE Part<>'As Above']. AS b

Same change appears in the second join statement.

Sorry I am not very up on SQL. Have you any idea why this is happeneing?

Thanks.

SELECT a.LineID, LAST(a.QuoteNo), LAST(a.Item), LAST(b.Part)
FROM

(myTable As a

INNER JOIN
(SELECT * FROM myTable WHERE Part<>'As Above') AS b
ON a.LineID >= b.LineID
)

INNER JOIN
(SELECT * FROM myTable WHERE Part <> 'As Above') AS c
ON a.LineID >= c.LineID

GROUP BY a.LineID, b.LineID

HAVING b.LineID=MAX(c.LineID)


should do.


Hoping it may help,
Vanderghast, Access MVP

I am trying to write a query for a colleague to pull out some data into
an excel file.

He is using a database which logs enquiries / quotes issued. However
under the detail section of the quote someone hase been entering "As
Above" in the part number field. This looks fine on the form view, but
when pulled out into excel obviously is of no use.

The relevant parts of the table are as follows:

LineID QuoteNo Item Part
1 100 1 Apple
2 100 2 As Above
3 101 1 Banana
4 102 1 Orange
5 102 2 Grape
6 102 3 As Above
7 102 4 As Above

I am trying to write a query to find the correct Part value when As
Above has been entered.

The logic is quite simple: If the Part value for LineID is "As Above",
lookup the Part value for LineID-1, then LineID-2 etc until you find
the a Part Value which is not "As Above". This will then be the correct
Part value.

Can something like this be done in SQL, or is there another way of
acheiving this result?

Any ideas appreciated.
Thanks,
Garry.