473,320 Members | 1,572 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

finding the Location of an Item at the Date it was last moved (Max of Date)

I've found severally topics that are close to what I'm trying to do, but as a beginner with SQL, I haven't been able to apply them to my situation.

I'm using Access 2002. It's an inventory tracking database.

I have 2 tables:

tblItems and tblLocation with the following fields (simplified):

tbl Items
Barcode (primary key)
Type
SubType

tblLocation
ID (Autonumber primary key)
Barcode
Location
Date

All Barcodes have Locations (no nulls). Each Barcode (item) moves around a lot and I would like to desgin a query that returns all the barcodes for items that have gone out "On Loan" the most recent time they are moved. In other words, find the max Date for all the Barcodes (up to here is not a problem), and then search that information for a Location that includes "On Loan" (Like "*" & "Loan" & "*").

When I query just Barcode and Max Date I get one max Date for each barcode, but when I add the Location field, I get all the Locations for each Barcode and the Date for each Location entry. I know I am one step away. I apologize for my SQL ignorance.

How do I find the Max Date for each Barcode and the corresponding Location of each Barcode at that Max Date only? Once I found the most recent Location, I could then filter that for Locations containing "On Loan".

Thanks in advance for your help.
Nov 7 '07 #1
14 1546
MMcCarthy
14,534 Expert Mod 8TB
Try this ....

Expand|Select|Wrap|Line Numbers
  1. SELECT Barcode, Max([Date]) As MaxDate, DLookup("[Location]","tblLocation","[Barcode]=" & [Barcode] & " AND [Date]=#" & MaxDate & "#") As Loc
  2. FROM tblLocation
  3. GROUP BY Barcode;
  4.  
Nov 8 '07 #2
Thanks for your help. :)

I'm getting a Data mismatch error.

I have General Date as my default Format for the Date field (which by the way I have since changed to LocDate after reading several threads), but the original dates for all these items are in Short Date format.

Do I need to change these entries all to General Date in order for this to work?
Nov 9 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Not sure what you mean by changing the entries. If LocDate is in General Date Format then all the dates should have changed automatically as long as they were in date format. Is Barcode a number or a text datatype?

Expand|Select|Wrap|Line Numbers
  1. SELECT Barcode, Max([LocDate]) As MaxDate, DLookup("[Location]","tblLocation","[Barcode]=" & [Barcode] & " AND [LocDate]=#" & MaxDate & "#") As Loc
  2. FROM tblLocation
  3. GROUP BY Barcode;
  4.  
Nov 9 '07 #4
Barcode is a text field because the first number of the 6 digit number on some items is a zero.

What I mean to say about the date is that, when the MaxLocDate column appears, some of the dates are in General Format and some are in Short Format.

I have the LocDate set to default to Now() in the table. When I entered the original locations for all the items, I entered only the date (mm/dd/yyyy) and not the time. All the movement since I designed the entry form all the new location change records have been stamped with a General Format automatically by Now(). I should clarify. The location list with each items original location was imported from Excel which was being used to track movement before I came along, and since then all of the dates were entered automatically in General format.

Do I need to use the Format function to convert all the dates before I use DLookup?

Thanks again for your help.
Nov 9 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
You might need to use format to change the date. For now though try changing the datatype of LocDate to ShortDate and see if that solves the problem. Also as Barcode is a text field you will need to change the code to the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT Barcode, Max([LocDate]) As MaxDate, DLookup("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#") As Loc
  2. FROM tblLocation
  3. GROUP BY Barcode;
  4.  
I've just surrounded the Barcode with single quote.
Nov 9 '07 #6
That was it! Thank you so much for your help, I really appreciate it.

If you don't mind, why is a text field a single quotation rather than a standard double?

This is a great forum. Although I just joined, I've been getting answers on this site from Google searched for a while. I'll make sure to search around and see if I can answer somebodies question and do my part.

Thanks again.
Nov 9 '07 #7
Nevermind the stupid question about the quotation marks...
Nov 9 '07 #8
MMcCarthy
14,534 Expert Mod 8TB
Nevermind the stupid question about the quotation marks...
:)

Not a problem. Glad its working for you.

Mary
Nov 9 '07 #9
I hate to bug you again, but when I try to enter Like "*" & "Loan" & "*" into the criteria for the calculated expression Loc, I get a pop up Enter Parameter box for Max Date

[PHP]SELECT tblLocation.Barcode, Max(tblLocation.LocDate) AS MaxDate, DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#") AS Loc
FROM tblLocation
GROUP BY tblLocation.Barcode
HAVING (((DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#")) Like "*" & "Loan" & "*"));[/PHP]

Do I need to reassert that MaxDate is Max(LocDate) in the HAVING statement?
Nov 9 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
Try this first ...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLocation.Barcode, Max(tblLocation.LocDate) AS MaxDate, DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#") AS Loc
  2. FROM tblLocation
  3. GROUP BY tblLocation.Barcode
  4. HAVING (((DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#")) LIKE '*Loan*'))
  5.  
Nov 9 '07 #11
I still get a Enter Parameter Value for MaxDate.
Nov 9 '07 #12
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT tblLocation.Barcode, Max(tblLocation.LocDate) AS MaxDate, DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & MaxDate & "#") AS Loc
  2. FROM tblLocation
  3. GROUP BY tblLocation.Barcode
  4. HAVING (((DLookUp("[Location]","tblLocation","[Barcode]='" & [Barcode] & "' AND [LocDate]=#" & Max(tblLocation.LocDate) & "#")) LIKE '*Loan*'))
  5.  
Nov 9 '07 #13
That was it. I think I learned quite a bit about SQL by reading through your code. Thank you so much.
Nov 9 '07 #14
MMcCarthy
14,534 Expert Mod 8TB
That was it. I think I learned quite a bit about SQL by reading through your code. Thank you so much.
You're more than welcome.
Nov 9 '07 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Bayla Frankl | last post by:
Hi all, I am a little stumped. I have a query I am trying to run to retrieve the last Progress Note record from the database for the current patient so that the therapists can see the last note...
5
by: Brian Henry | last post by:
Say I put an item into a panel and place it onto a form, and I want to know where on the form the item is in X,Y positions... but If I check the location property of the item in the panel, that is...
5
by: alanspamenglefield | last post by:
Hello group, I have an SQL statement which pulls data from a table as follows: " SELECT tblSites.sites_siteno, " & _ " tblSites.sites_sitename, " & _ " Sum(tblStockResults.stkr_result) AS...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
0
by: Petr Man | last post by:
Hello everyone, I have a repeatedly running process, which always creates a new logfile with an ending n+1. What I need is to find the last file, the one with highest number at the end. The...
2
by: Extremest | last post by:
Here is the code I have so far. It connects to a db and grabs headers. It then sorts them into groups and then puts all the complete ones into another table. Problem I am having is that for some...
9
by: ice | last post by:
Hello, I have a couple of tables. The client tables and the contacted tables. I am not sure how to start on this, what I need is a way to query all my clients then show any client that the...
4
by: andreas.hei | last post by:
How can I get the value of the last Item in a coums? SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC? something with Fields(0).value? Thank you in advance
3
by: peter.mosley | last post by:
I've tried googling for the answer to this problem, without any luck. I'm sure the truth must be out there somewhere! I have a multiselect listbox populated with many items (set by the RowSource...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.