John,
This is what I've come up with but it prompts me for I2.CustID_32 and I2.Prtnum_32.
(SELECT Max([Invoice Detail].INVDTE_32) AS I2
FROM [Invoice Detail]
WHERE I2.CustID_32 = [Invoice Detail].CustID_32 AND I2.PRTNUM_32 = [Invoice Detail].PRTNUM_32)
Doyce
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Doyce-
John,
Thanks for your help. The sub-report is linked on CustID_32 and PRTNUM_32. Here is the SQL of the sub-report:
SELECT [Invoice Detail].CUSTID_32, [Invoice Detail].PRTNUM_32, [Invoice Detail].ORDNUM_32, [Invoice Detail].INVDTE_32, [Invoice Detail].STYPE_32, [Part Master].PMDES1_01, [Invoice Detail].INVQTY_32, ([Cogs_32]/[invqty_32]) AS Cost, [Invoice Detail].COGS_32, [Invoice Detail].PRICE_32, IIf([STYPE_32]="CR",-[invqty_32]*[Price_32],[invqty_32]*[Price_32]) AS Price_Extension, Round(([Price_Extension]-[Cogs_32])/[Price_Extension]*100,2) AS [Gross Margin]
FROM [Invoice Detail] INNER JOIN [Part Master] ON [Invoice Detail].PRTNUM_32 = [Part Master].PRTNUM_01;
Doyce
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Which field has the date?
You'll need to add something like:
WHERE [Date Field] =
(SELECT Max([Date Field]) FROM [Invoice Detail} As I2
WHERE I2.CustID_32 = [Invoice Detail].CustID_32
AND I2.PRTNUM_32 = [Invoice Detail].PRTNUM_32)
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 2, 2017, at 5:33 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Thanks for your help. The sub-report is linked on CustID_32 and PRTNUM_32. Here is the SQL of the sub-report:
SELECT [Invoice Detail].CUSTID_32, [Invoice Detail].PRTNUM_32, [Invoice Detail].ORDNUM_32, [Invoice Detail].INVDTE_32, [Invoice Detail].STYPE_32, [Part Master].PMDES1_01, [Invoice Detail].INVQTY_32, ([Cogs_32]/[invqty_32]) AS Cost, [Invoice Detail].COGS_32, [Invoice Detail].PRICE_32, IIf([STYPE_32]="CR",-[invqty_32]*[Price_32],[invqty_32]*[Price_32]) AS Price_Extension, Round(([Price_Extension]-[Cogs_32])/[Price_Extension]*100,2) AS [Gross Margin]
FROM [Invoice Detail] INNER JOIN [Part Master] ON [Invoice Detail].PRTNUM_32 = [Part Master].PRTNUM_01;
Doyce
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Doyce-
Need more details. What's the Record Source of the subreport and what are the Link Master and Link Child properties?
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 2, 2017, at 4:45 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello Pros,
I have a report that details credit memos where parts have been returned. I added a sub report that shows the sales of that part to the customer. How can I limit the sub-report to show only the last sale of the part and not all the sales of the part?
Doyce
__._,_.___
Posted by: winberry.doyce@con-way.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar