如何制定此访问查询 [英] How to Formulate this Access Query

问题描述

我有一个包含销售数据的访问数据库表，详细说明了销售代表，客户，销售日期，销售项目等。

我需要生成一个查询，列出哪些客户在上周，月，6个月（输入变量）和负责商店的销售代表没有带给我们。

例如

客户名称，代表，上次销售日期/最近一次销售

商店a，tom，17/3/2011

store b，joe，1/1/2011

然后我知道乔需要去商店b并进行销售，因为他们最后没有从我们这里购买任何东西3个月。我的问题一直在计算最新的销售日期。在excel中，我会计算自每家公司销售后的天数，然后使用小功能进行最近的销售。


谢谢


Alex

Hi,

I have a access database table with sales data in it, detailing sales rep, customer, date of sale, sale item etc.

I need to generate a query which will list which customers haven''t brought off us in the last week, month, 6 months (input variable) and the sales rep responsible for the store.
eg
customer name, rep, date of last sale/ most recent sale
store a, tom, 17/3/2011
store b, joe, 1/1/2011

Then i know joe needs to go to store b and make a sale because they haven''t purchased anything from us in the last 3 months. My problem has been calculating the lastest sales date. In excel i would calculate the number of days since sale for each company then use the small function to the most recent sale.

Thanks

Alex

推荐答案

这看起来非常简单。假设所有细节都是准确的。


您要查找的是所有记录的列表，其中存储的日期小于当前日期 - 可变因子。然后，第一步是将为期间输入的值（1周; 1个月; 3个月;等）转换为可识别的SQL日期文字（参见 Literal DateTimes及其分隔符（＃））。我怀疑在这里使用 datPeriod = DateAdd（？，？，Date（））会很好。我没有足够的细节来指定两个缺失的参数，但我相信你可以解决它们。


假设那么你现在有一个变量（ datPeriod）设置的值反映了您想要扫描的句点的开始，您可以创建以下字符串变量（strSQL）来包含您需要用来列出表中相关项的SQL： />

This seems pretty straightforward Alex. Assuming all the details are accurate.

What you''re looking to find is a list of all records where the Date stored is less than the current date - a variable factor. The first step then is to convert the value entered for the period (1 week; 1 month; 3 months; etc) into a recognisable SQL date literal (See Literal DateTimes and Their Delimiters (#)). I suspect that using datPeriod = DateAdd(?, ?, Date()) would be good here. I don''t have enough detail from you to specify the two missing parameters but I''m sure you can work them out.

Assuming then, that you now have a variable (datPeriod) set up with the value that reflects the start of the period you want to scan back over, you can create the following string variable (strSQL) to contain the SQL you need to use to list the relevant items from your table :

展开 | 选择 | Wrap | 行号

您好NeoPa，


感谢您的回复。


Usi你的代码会告诉我在一段时间之前所做的所有销售。因此，公司A可能会在该列表中出现50次。但是不要关心其中的49个。我只需要知道该公司最近的销售情况。


我需要一份我卖给的所有公司的清单，以及最近一次销售给该公司的日期。


再次感谢


Alex

Hi NeoPa,

Thanks for your response.

Using your code would tells me all the sales made before a certain period of time. So company A may appear 50 times in that list. But don''t care about 49 of them. I just need know the most recent sale to that company.

Bescially I need a list of every compnay i sell to, with the date of most recent sale to that company.

Thanks again

Alex

通过添加<$ c简单地调整NeoPa''a查询$ c> SELECT TOP 1 * 和 GROUP BY [LastSaleDate] DESC

这篇关于如何制定此访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！