Thanks Daniel,
I am in the process of downloading it. It will take 1hr 8min. Thats living in Africa for you.
Alex
AlexMwells
Last Activity: May 30 '11, 11:10 PM
Joined: Mar 17 '11
Location:
-
Hi Rabbit,
Thank you very much for your response. Grouping by month was a very good idea. For anyone who stores their accounting data in Pastel Partner 2009 the following code should work.
...Code:SELECT datepart("m",[HistoryLines.DDate]) as Month,CustomerMaster.CustomerCode, HistoryHeader.DelAddress01, HistoryHeader.DelAddress02, HistoryLines.Description, Sum(HistoryLines.Qty) as TotalQty, Sum(HistoryLines.DiscountAmount)Leave a comment:
-
SQL Advanced Query Outputting to Excel
Hi,
I'm using the following code to find the total sales of each product to each customer, for a user choosen month.
...Code:SELECT HistoryHeader.DelAddress01 as Shop, HistoryLines.Description, Sum(HistoryLines.DiscountAmount) AS TotalQty FROM CustomerMaster INNER JOIN (HistoryHeader INNER JOIN HistoryLines ON HistoryHeader.DocumentNumber=HistoryLines.DocumentNumber) ON CustomerMaster.CustomerCode=HistoryHeader.CustomerCode
-
Hi,
I actually spotted them as soon as I posted. The correct code is given below.
...Code:SELECT HistoryLines.DDate, CustomerMaster.CustomerDesc, HistoryLines.Description, HistoryLines.Qty, HistoryLines.UnitPrice, HistoryLines.DiscountAmount FROM HistoryLines INNER JOIN CustomerMaster ON HistoryLines.CustomerCode = CustomerMaster.CustomerCode WHERE datepart("ww",[HistoryLines.DDate],2)=datepart("ww",Date(),2)Leave a comment:
-
SQL code for returning this weeks salesdata
Hi,
Can anybody spot the error in my code please
...Code:SELECT HistoryLines.DDate, CustomerMaster.CustomerDesc, HistoryLines.Description, HistoryLines.Qty, HistoryLines.UnitPrice, HistoryLines.DiscountAmount FROM HistoryLines INNER JOIN CustomerMaster ON HistoryLines.CustomerCode = CustomerMaster.CustomerCode WHERE datepart("ww"[HistoryLines.DDate],2)=datepart("ww",[Date()],2); -
thank you very much
Has worked exactly as I needed itCode:SELECT CustomerTable.[Shop Number], CustomerTable.[Sales rep], Max(SalesTable.[SaleDate]) AS LastSaleDate FROM CustomerTable INNER JOIN SalesTable ON CustomerTable.[Shop Number] = SalesTable.ShopNumber GROUP BY CustomerTable.[Shop Number], CustomerTable.[Sales rep];
Thanks Again
AlexLeave a comment:
-
Hi NeoPa and Mariostg,
Thanks for your help I will try working on it today. Yesteday was my first day using access. I had used WQL before, but not SQL. ThanksLeave a comment:
-
Hi MarioStg,
Thanks for the reply
Your idea works well thanks. I am currently using the following SQl query
...Code:SELECT TOP 1 SalesTable.ShopNumber, CustomerTable.[Shop name], CustomerTable.[Sales rep], SalesTable.SaleDate, Date()-[saledate] AS DaysAgo FROM CustomerTable INNER JOIN SalesTable ON CustomerTable.[Shop Number] = SalesTable.ShopNumber WHERE (((SalesTable.ShopNumber)=[Which shop number?])) ORDER
Leave a comment:
-
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
A...Leave a comment:
-
How to Formulate this Access Query
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,...
No activity results to display
Leave a comment: