在上次移动的日期查找项目的位置（最大日期） [英] finding the Location of an Item at the Date it was last moved (Max of Date)
问题描述
我找到了几个与我正在尝试的内容相近的主题，但作为SQL的初学者，我还没能将它们应用到我的情况中。
我正在使用Access 2002.它是一个库存跟踪数据库。
我有2个表：
tblItems 和 tblLocation 包含以下字段（简化）：
tbl项目
条形码（主键）
类型
子类型
tblLocation
ID（自动编号主键）
条形码
位置
日期
所有条形码都有位置（无空值）。每个条形码（项目）移动很多，我想设计一个查询，返回已经出去的项目的所有条形码On Loan。他们被移动了最近的时间。换句话说，找到所有条形码的最大日期（到此处不是问题），然后搜索包含在贷款的位置的信息。 （比如*&"""&" *"）。
当我查询条形码和最大日期时，我会为每个条形码获得一个最大日期，但是当我添加位置字段时，我会获得每个条形码的所有位置和每个位置条目的日期。我知道我离我只有一步之遥。我为我的SQL无知道歉。
如何找到每个条形码的最大日期以及每个条形码在最大日期的相应位置？一旦找到最新的位置，我就可以对包含On Loan的地点进行过滤。
提前感谢您的帮助。
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.
推荐答案
尝试这个....
Try this ....
感谢您的帮助。 :)
我收到数据不匹配错误。
我将General Date作为Date字段的默认格式（顺便说一下，我读完几个帖子后改为LocDate，但所有这些项目的原始日期都是短日期格式。
我是否需要将这些条目全部更改为一般日期以便这个工作？
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?
通过更改条目不确定你的意思。如果LocDate采用通用日期格式，则只要日期格式为日期，所有日期都应自动更改。条形码是数字还是文本数据类型？
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?
这篇关于在上次移动的日期查找项目的位置（最大日期）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
