By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Current Location Query (Recently Posted as Please Help)

P: n/a
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

TransactionNumber 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(without
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.
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 15 Oct 2004 19:51:03 -0700, he****@gmail.com (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.RMANumber=query1.RMANumber and table1.TimeAndDate =
query1.TimeAndDate

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.RMANumber, table2.Location
from query2 inner join table2 on
query1.LocationSer = table2.LocationSer

-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

TransactionNumber 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(without
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.


Nov 13 '05 #2

P: n/a
"Mike" <he****@gmail.com> wrote in message
news:53**************************@posting.google.c om...
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

TransactionNumber 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(without
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.TimeandDate)
from Table1 as t3
where t3.RMANumber = t1.RMANumber
)
I suggest giving your tables name which are meaningful like "transactions"
and "locations"

Nov 13 '05 #3

P: n/a

"Mike" <he****@gmail.com> skrev i en meddelelse
news:53**************************@posting.google.c om...
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

TransactionNumber 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(without
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.LocationSer, Last(Table2.LocationName)
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table1.LocationSer

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
Nov 13 '05 #4

P: n/a
> SELECT Table1.LocationSer, Last(Table2.LocationName)
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table1.LocationSer


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

SELECT Table1.RMANumber, Last(Table2.LocationName)
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table1.RMANumber

Nov 13 '05 #5

P: n/a

"Kaj Julius" <ju******@lindbjergparken.nospm.dk> skrev i en meddelelse
news:41*********************@dread11.news.tele.dk. ..
SELECT Table1.LocationSer, Last(Table2.LocationName)
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table1.LocationSer


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

SELECT Table1.RMANumber, Last(Table2.LocationName)
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table1.RMANumber


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

SELECT Last(Table1.RMANumber), Table2.LocationName
FROM Table1 INNER JOIN Table2 ON Table1.LocationSer = Table2.LocationSer
GROUP BY Table2.LocationsName

Hopefully this ship sails...

Regards,
Kaj

Nov 13 '05 #6

P: n/a
Thanks Guys, It works great now. I wasn't linking all of the
informatio that I needed .
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.