Current Location Query (Recently Posted as Please Help)

[Mike]

I have had alot of problems with my query, I have a two tables of
informations and just need a simple Query to get what I want but it is
turning out not to be so simple.
Table 1 Has the following information

TransactionNumb er LocationSer RMANumber TimeandDate
1 0000045679 234567 10/10/2004 1:30:45PM
2 0000023456 234567 10/10/2004 2:30:24PM
3 0000045679 234678 10/10/2004 11:30:25AM

Table 2 Has this information

LocationSer LocationName
0000045679 Recieving
0000023456 Inspection

What I am trying to do is create a query, or a series of queries, that
will give me the CURRENT location for every RMANumber(witho ut
duplicating the RMANumber). I would like it to resemble this

RMANumber Location
234567 Inspeaction
234678 Recieving

Please notice that the RMANumber occurs only once in the query but
twice in Table1.I am probably over thinking the problem. The answer
can be in SQL or Access directions. Any help will be greatly
appreciated. Once I see it done correctly I am sure it will become
obvious.

[Tom van Stiphout]

On 15 Oct 2004 19:51:03 -0700, he****@gmail.co m (Mike) wrote:

Try this (off the top of my head - excuse minor syntax issues). Some
SQL gurus will do this in 1 query. I prefer to maintain my sanity and
break the problem down in bite-size chunks.

First create a query that gets the most recent date for each RMA:
Select RMANumber, max(TimeAndDate )
from table1
group by RMANumber

Then create a second query to pick up the LocationSer for each record
in query1. Put both query1 and table1 on the design surface, and inner
join both RMANumber and TimeAndDate:
select LocationSer, query1.*
from table1 inner join query1 on
table1.RMANumbe r=query1.RMANum ber and table1.TimeAndD ate =
query1.TimeAndD ate

Then a third query will pull the LocationName:
(btw, that's spelled 'Receiving')
Put both query2 and table2 on the design surface, and inner join by
LocationSer:
select query2.RMANumbe r, table2.Location
from query2 inner join table2 on
query1.Location Ser = table2.Location Ser

-Tom.

I have had alot of problems with my query, I have a two tables of
informations and just need a simple Query to get what I want but it is
turning out not to be so simple.
Table 1 Has the following information

TransactionNum ber LocationSer RMANumber TimeandDate
1 0000045679 234567 10/10/2004 1:30:45PM
2 0000023456 234567 10/10/2004 2:30:24PM
3 0000045679 234678 10/10/2004 11:30:25AM

Table 2 Has this information

LocationSer LocationName
0000045679 Recieving
0000023456 Inspection

What I am trying to do is create a query, or a series of queries, that
will give me the CURRENT location for every RMANumber(witho ut
duplicating the RMANumber). I would like it to resemble this

RMANumber Location
234567 Inspeaction
234678 Recieving

Please notice that the RMANumber occurs only once in the query but
twice in Table1.I am probably over thinking the problem. The answer
can be in SQL or Access directions. Any help will be greatly
appreciated. Once I see it done correctly I am sure it will become
obvious.

[John Winterbottom]

"Mike" <he****@gmail.c om> wrote in message
news:53******** *************** ***@posting.goo gle.com...

I have had alot of problems with my query, I have a two tables of
informations and just need a simple Query to get what I want but it is
turning out not to be so simple.
Table 1 Has the following information

TransactionNumb er LocationSer RMANumber TimeandDate
1 0000045679 234567 10/10/2004 1:30:45PM
2 0000023456 234567 10/10/2004 2:30:24PM
3 0000045679 234678 10/10/2004 11:30:25AM

Table 2 Has this information

LocationSer LocationName
0000045679 Recieving
0000023456 Inspection

What I am trying to do is create a query, or a series of queries, that
will give me the CURRENT location for every RMANumber(witho ut
duplicating the RMANumber). I would like it to resemble this

RMANumber Location
234567 Inspeaction
234678 Recieving

Please notice that the RMANumber occurs only once in the query but
twice in Table1.I am probably over thinking the problem. The answer
can be in SQL or Access directions. Any help will be greatly
appreciated. Once I see it done correctly I am sure it will become
obvious.

select t1.RMANumber, t2.LocationName
from Table1 as t1
inner join Table2 as t2
on t1.LocationSer = t2.LocationSer
where t1.TimeandDate =
(
select max(t3.TimeandD ate)
from Table1 as t3
where t3.RMANumber = t1.RMANumber
)
I suggest giving your tables name which are meaningful like "transactio ns"
and "locations"

[Kaj Julius]

"Mike" <he****@gmail.c om> skrev i en meddelelse
news:53******** *************** ***@posting.goo gle.com...

I have had alot of problems with my query, I have a two tables of
informations and just need a simple Query to get what I want but it is
turning out not to be so simple.
Table 1 Has the following information

TransactionNumb er LocationSer RMANumber TimeandDate
1 0000045679 234567 10/10/2004 1:30:45PM
2 0000023456 234567 10/10/2004 2:30:24PM
3 0000045679 234678 10/10/2004 11:30:25AM

Table 2 Has this information

LocationSer LocationName
0000045679 Recieving
0000023456 Inspection

What I am trying to do is create a query, or a series of queries, that
will give me the CURRENT location for every RMANumber(witho ut
duplicating the RMANumber). I would like it to resemble this

RMANumber Location
234567 Inspeaction
234678 Recieving

Please notice that the RMANumber occurs only once in the query but
twice in Table1.I am probably over thinking the problem. The answer
can be in SQL or Access directions. Any help will be greatly
appreciated. Once I see it done correctly I am sure it will become
obvious.

SELECT Table1.Location Ser, Last(Table2.Loc ationName)
FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table1.Location Ser

The use of this simple query is based on the assumption that the
transactions are entered as they occur, ie in ascending time order. Then the
use of LAST would ensure that the latest (and current) location is selected.

If this requirement cannot be guarantied, the more complex query proposed by
John Winterbottom is better.

Regards,
Kaj

[Kaj Julius]

> SELECT Table1.Location Ser, Last(Table2.Loc ationName)

FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table1.Location Ser

Oops, sorry. Wrong field selected/grouped. The correct query is of course

SELECT Table1.RMANumbe r, Last(Table2.Loc ationName)
FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table1.RMANumbe r

[Kaj Julius]

"Kaj Julius" <ju******@lindb jergparken.nosp m.dk> skrev i en meddelelse
news:41******** *************@d read11.news.tel e.dk...

SELECT Table1.Location Ser, Last(Table2.Loc ationName)
FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table1.Location Ser

Oops, sorry. Wrong field selected/grouped. The correct query is of course

SELECT Table1.RMANumbe r, Last(Table2.Loc ationName)
FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table1.RMANumbe r

Oh man, I'm getting old and stupid!!! Okay, third try

SELECT Last(Table1.RMA Number), Table2.Location Name
FROM Table1 INNER JOIN Table2 ON Table1.Location Ser = Table2.Location Ser
GROUP BY Table2.Location sName

Hopefully this ship sails...

Regards,
Kaj

[Mike]

Thanks Guys, It works great now. I wasn't linking all of the
informatio that I needed .