How do I choose the 1st invoice record from each customer?

I need to run a query to pull the 1st invoice from each new customer for the 
year.  Here is what I have done so far (probably not correct, but I tried).

Thanks, 
Art

SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], [AJG 
revised 1st invoice date].[Customer Name], Shipments.invoice_number, 
Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number, 
Shipments.ar_name
FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON 
Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
Where Shipments.Invoice_number in (Select Top 1 Shipments.Invoice_number
From Shipments as 1
Where Shipments.invoice_number = Shipments.Invoice_number
Order by Shipments.Invoice_number)

0
Utf
1/15/2008 10:24:01 PM
access 16762 articles. 2 followers. Follow

5 Replies
698 Views

Similar Articles

[PageSpeed] 39

Art,
something like this

SELECT [AJG revised 1st invoice date].[Customer Number], [AJG
revised 1st invoice date].[Customer Name], Shipments.invoice_number, 
Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number, 
Shipments.ar_name
FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON 
Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
WHERE (Select Min(Shipments.invoice_date) As MinOfDate From Shipments FROM 
Shipments INNER JOIN [AJG revised 1st invoice date] ON Shipments.ar_number = 
[AJG revised 1st invoice date].[Customer Number])
ORDER BY Shipments.Invoice_number;

The above assumes that your data only has records for the current year.

Jeanette Cunningham
"Art@ISCO" <ArtISCO@discussions.microsoft.com> wrote in message 
news:6FE5FE9D-7AB2-4D69-9AF7-131E99CBE596@microsoft.com...
>I need to run a query to pull the 1st invoice from each new customer for 
>the
> year.  Here is what I have done so far (probably not correct, but I 
> tried).
>
> Thanks,
> Art
>
> SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], [AJG
> revised 1st invoice date].[Customer Name], Shipments.invoice_number,
> Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number,
> Shipments.ar_name
> FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON
> Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
> Where Shipments.Invoice_number in (Select Top 1 Shipments.Invoice_number
> From Shipments as 1
> Where Shipments.invoice_number = Shipments.Invoice_number
> Order by Shipments.Invoice_number)
> 


0
Jeanette
1/15/2008 11:40:51 PM
On Tue, 15 Jan 2008 14:24:01 -0800, Art@ISCO
<ArtISCO@discussions.microsoft.com> wrote:

>I need to run a query to pull the 1st invoice from each new customer for the 
>year.  Here is what I have done so far (probably not correct, but I tried).
>
>Thanks, 
>Art
>
>SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], [AJG 
>revised 1st invoice date].[Customer Name], Shipments.invoice_number, 
>Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number, 
>Shipments.ar_name
>FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON 
>Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
>Where Shipments.Invoice_number in (Select Top 1 Shipments.Invoice_number
>From Shipments as 1
>Where Shipments.invoice_number = Shipments.Invoice_number
>Order by Shipments.Invoice_number)

Well, this will find those records where Shipments.invoice_number is equal to
itself. Since everything is by definition equal to itself, that's all the
records, and your WHERE clause does exactly nothing.

What is [AJG Revised 1st Invoice date]?
Why the "as 1"? What's that intended to do?

Maybe this needs to be:

SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], 
[AJG revised 1st invoice date].[Customer Name], 
Shipments.invoice_number, Shipments.invoice_date, Shipments.invoice_amount,
Shipments.ar_number, Shipments.ar_name
FROM Shipments 
INNER JOIN [AJG revised 1st invoice date] 
ON Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
Where Shipments.Invoice_number in 
(Select Top 1 Shipments.Invoice_number From Shipments as Latest
Where Latest.invoice_number = Shipments.Invoice_number
AND Latest.invoice_date >= DateSerial(Year(Date()), 1, 1)
Order by Shipments.Invoice_number DESC);

You want the earliest shipment *this year*, not the most recent shipment,
hence the additiona invoice_date criterion and the DESC.


             John W. Vinson [MVP]
0
John
1/15/2008 11:43:39 PM
Hello All, 
I am redoing the query and here is what I have so far:
This query is pulling all the invoices for the customers setup in 2007.  I 
need only the first record.

Thanks for your help.
Art  
SELECT DISTINCT RM00101USA.CUSTNMBR, RM00101USA.CUSTNAME, 
RM00101USA.CUSTCLAS, RM00101USA.CREATDDT, Shipments.invoice_number, 
Shipments.invoice_date, RM00101USA.ADDRESS1, RM00101USA.ADDRESS2, 
RM00101USA.CITY, RM00101USA.STATE, Shipments.invoice_amount
FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNMBR = 
Shipments.ar_number
WHERE (((RM00101USA.CREATDDT)>#12/31/2006#));



"Art@ISCO" wrote:

> I need to run a query to pull the 1st invoice from each new customer for the 
> year.  Here is what I have done so far (probably not correct, but I tried).
> 
> Thanks, 
> Art
> 
> SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], [AJG 
> revised 1st invoice date].[Customer Name], Shipments.invoice_number, 
> Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number, 
> Shipments.ar_name
> FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON 
> Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
> Where Shipments.Invoice_number in (Select Top 1 Shipments.Invoice_number
> From Shipments as 1
> Where Shipments.invoice_number = Shipments.Invoice_number
> Order by Shipments.Invoice_number)
> 
0
Utf
1/16/2008 5:49:02 PM
Art,
try this

SELECT RM00101USA.CUSTNBR, RM00101USA.CUSTNAME, RM00101USA.CUSTCLAS, 
RM00101USA.CREATDDT, Shipments.invoice_number, Shipments.invoice_date, 
RM00101USA.ADDRESS1, RM00101USA.ADDRESS2, RM00101USA.CITY, RM00101USA.STATE, 
Shipments.invoice_amount
FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNBR = 
Shipments.ar_number
WHERE (((Shipments.invoice_date)=(SELECT Min(Shipments.invoice_date) AS 
MinOfinvoice_date FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNBR 
= Shipments.ar_number) And [RM00101USA].[CREATDDT]>#12/31/2006#));

Jeanette Cunningham

"Art@ISCO" <ArtISCO@discussions.microsoft.com> wrote in message 
news:C1D2E726-D12E-4A7E-8982-E012553480B5@microsoft.com...
> Hello All,
> I am redoing the query and here is what I have so far:
> This query is pulling all the invoices for the customers setup in 2007.  I
> need only the first record.
>
> Thanks for your help.
> Art
> SELECT DISTINCT RM00101USA.CUSTNMBR, RM00101USA.CUSTNAME,
> RM00101USA.CUSTCLAS, RM00101USA.CREATDDT, Shipments.invoice_number,
> Shipments.invoice_date, RM00101USA.ADDRESS1, RM00101USA.ADDRESS2,
> RM00101USA.CITY, RM00101USA.STATE, Shipments.invoice_amount
> FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNMBR =
> Shipments.ar_number
> WHERE (((RM00101USA.CREATDDT)>#12/31/2006#));
>
>
>
> "Art@ISCO" wrote:
>
>> I need to run a query to pull the 1st invoice from each new customer for 
>> the
>> year.  Here is what I have done so far (probably not correct, but I 
>> tried).
>>
>> Thanks,
>> Art
>>
>> SELECT DISTINCT [AJG revised 1st invoice date].[Customer Number], [AJG
>> revised 1st invoice date].[Customer Name], Shipments.invoice_number,
>> Shipments.invoice_date, Shipments.invoice_amount, Shipments.ar_number,
>> Shipments.ar_name
>> FROM Shipments INNER JOIN [AJG revised 1st invoice date] ON
>> Shipments.ar_number = [AJG revised 1st invoice date].[Customer Number]
>> Where Shipments.Invoice_number in (Select Top 1 Shipments.Invoice_number
>> From Shipments as 1
>> Where Shipments.invoice_number = Shipments.Invoice_number
>> Order by Shipments.Invoice_number)
>> 


0
Jeanette
1/16/2008 7:53:54 PM
On Wed, 16 Jan 2008 09:49:02 -0800, Art@ISCO
<ArtISCO@discussions.microsoft.com> wrote:

>Hello All, 
>I am redoing the query and here is what I have so far:
>This query is pulling all the invoices for the customers setup in 2007.  I 
>need only the first record.
>
>Thanks for your help.
>Art  
>SELECT DISTINCT RM00101USA.CUSTNMBR, RM00101USA.CUSTNAME, 
>RM00101USA.CUSTCLAS, RM00101USA.CREATDDT, Shipments.invoice_number, 
>Shipments.invoice_date, RM00101USA.ADDRESS1, RM00101USA.ADDRESS2, 
>RM00101USA.CITY, RM00101USA.STATE, Shipments.invoice_amount
>FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNMBR = 
>Shipments.ar_number
>WHERE (((RM00101USA.CREATDDT)>#12/31/2006#));

The first record for each custnmbr in creatddt order? "First record" is
ambiguous...

If so:

SELECT  RM00101USA.CUSTNMBR, RM00101USA.CUSTNAME, 
RM00101USA.CUSTCLAS, RM00101USA.CREATDDT, Shipments.invoice_number, 
Shipments.invoice_date, RM00101USA.ADDRESS1, RM00101USA.ADDRESS2, 
RM00101USA.CITY, RM00101USA.STATE, Shipments.invoice_amount
FROM RM00101USA INNER JOIN Shipments ON RM00101USA.CUSTNMBR = 
Shipments.ar_number
WHERE (((RM00101USA.CREATDDT)>#12/31/2006#))
AND Shipments.invoice_date = (SELECT Min([invoice_date]) FROM Shipments AS S
WHERE S.ar_number = RM00101USA.CUSTNMBR AND S.[invoice_date] >= #1/1/07# AND
S.invoice_date < #1/1/08#);

             John W. Vinson [MVP]

0
John
1/16/2008 8:02:17 PM
Reply:

Similar Artilces:

Calandar invite for two days to choose from
Can I create a meeting invitation that recurs on Tuesday and Thursday, but the invitees can select one or the other that they want to attend? In other words, I'll be having the same meeting twice a week and some will attend one and some the other but I want it in one invite. Any thoughts? Thanks! "Amy" <Amy@discussions.microsoft.com> wrote in message news:6824F50D-8E5F-4DD0-826A-6C01547BB1D8@microsoft.com... > Can I create a meeting invitation that recurs on Tuesday and Thursday, but > the invitees can select one or the other that they want to attend?...

HELP! **Transfer orders to invoices error
I need some serious help. We just upgraded from GP 7.5 to GP 8.0 and now I am unable to transfer orders to invoices. I keep getting an error that says "Order ORD1555 has no line items to transfer to Invoice INV144408." Order# 1555 does have about 10 line items, which I can view on the screen. I did go in and run the "Check Links" function, but it hasn't done anything. This is our busiest time of year and a really bad time for something like this to happen. Can someone please give me some advice on how to fix this? if this is just one order, why not cancel...

Counting Records in All Pages of a View
i have to calculate total leads, is there any way to calculate all the records in all pages of any view( like leads) Hi Mahain, Out of the box crm does not offer you that funcitonality. You can create a sql query which will perform this query: select count(1) from filteredlead This you can use to fetch the total amount on all pages. Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://www.ronaldlemmen.com/ "Mahain" wrote: > i have to calculate total leads, is there any way to calculate all the > records in all pages of any view( like leads) > ...

Custom Frame
How do I define a custom frame in visual c++? Try deriving from whatever frameclass you want to implement. Simply declare a new frame class name by using a base + derived class syntax: class FrameClass // existing frameclass { public: FrameClass (); virtual ~FrameClass (); protected: // available as public: to derived name class below // etc... private: //private functions }; // all of the above not defined by you... // derived + base class synt...

Choose.
I am currently using a similar statement in a DB, how may I adapt it to an Excel cell please? (striking trouble with the divisor into the text string) =CHOOSE(FIND(VENUE,"W ANSWVICS AN TCANTAS",0)/3,1,2,3,4,5,6,7) Thank you. Hi Rodney, Try changing the zero to one. Gary Hi Gary, Yep, that fixed it nicely. appreciated. | Hi Rodney, | Try changing the zero to one. | Gary ...

Choose Application?
Version: 2008 Operating System: Mac OS X 10.0 (Cheetah) When I put the 2008 Update disk in my computer, the Installer window comes up. When I double click to start the installer, a message comes up to choose an application to open it with and I dont know what to do. All of the available applications it shows me are not opening it up. Help! That's your problem right there: "Mac OS X 10.0 (Cheetah)". Office 2008 requires OS X 10.4.9 (Tiger) or later... Have a look at the published System Requirements printed on the retail package or posted on the Mactopia web site Produc...

Emailing an Invoice
Is there a way for me to email a custome his invoice prior to shipping? Thank you. Lance, You could try printing it to a PDF file and send that. Good luck, Matt "Lance" <Lance@discussions.microsoft.com> wrote in message news:4299CCE6-94EC-466B-A19B-59FC16A3768F@microsoft.com... > Is there a way for me to email a custome his invoice prior to shipping? > > Thank you. To add to Matt, try www.pdf995.com from Software 995, it is free and it works. I use it all the time, for saving Tech Articles and such. Rob from Software995 "The free versions display a spo...

maximum rows custom list
Hi, I have about 2000 rows I want to add in a custom list in order to sort another column that contains 4000 rows. The problem is that the custom list only uses the first 180 rows or so. It seems like the maximum rows or items you can add to a custom list is 180. Does anybody know a solution to this problem.? p.s. already split the 2000 rows in 180 rows parts...this is tricky however...you tend to make mistakes... Please help, thanks in advance, Jootje Hi Jootie, A long custom list can be added programmatically. Try: '======================>> Public Sub Tester001() ...

Converting Access records/tables into Outlook contacts
Does anyone know how to do this? Have you tried using Outlook's File|Import and Export... menu command? On Fri, 25 Jul 2003 12:52:46 -0700, "Doug" <doug@cardia.biz> wrote: >Does anyone know how to do this? John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. ...

Workflow doesn't update with record changes
I've created a service activity wait for workflow that sends the owner an email based on the following criteria: the scheduled date has passed, and the status reason is at "scheduled." If I change the time or date, the workflow still notifies the owner according to the date that was entered at the time of the activity's creation. Is there any way to change this feature? We create a number of service activites that have their scheduled start dates changed. Thanks, Sarah ...

Recording Merger
I've a merger that the wizard doesn't completely handle. BAC & MBNA merged. BAC gave 1:2 shares + some cash. They paid CASH-LIEU for fractional shares. Thus I have the following activity on my statement which I'm trying to record in Money. Before MBNA - 32.7068 shares Now 1/3/06 BAC SHRS rec'd through merger = 16 1/3/06 MBNA Fee = $25 1/3/06 MBNA Merger = (32.7068) = $134.92 (I think this is the cash part of the merger. 1/9/06 BAC CIL On 0.38284 @ 46.62010 = $17.85 (I think this is payout of 1/2 the fractional share left after the 1:2 stoc...

customer look up
Hello, I went to a customers site today and they had a recall on an item and they went to the item and they are trying to see what customers have purchased this item so that they can contact them. Can you please tell me how this can be done? This is a multi-part message in MIME format. ------=_NextPart_000_0044_01C91CCE.6B38B910 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit Close RMS Manager Program if you are using it. Copy the attached report to 'Program Files/Microsoft Retail Management System/Store Op...

Project Invoice Printing Twice
When I print my Project Bills/Invoices, two different invoice formats print (both are complete invoices). They are both a similar named Report Writer form, one is named XYZ...Page One, with the other named XYZ...Page Two. Our staff configured the Page Two document to print in the desired format, so we don't need the first 'page'. Do I need to uncheck a flag somewhere? ...

how do i get records to append to multiple tables
I have 4 option buttons but when one is selected I would like the information in several fields to append to multiple tables and appear in the main form. I want this to happen automatically. I am new to Access Programming so any help is appreciated, but please be very clear since programming jargon is not at my finger tips. Thank you, -- Deenos Deenos It strikes me a bit unusual to have one form updating multiple tables. If you'll provide a bit more specific description of your tables and your form, folks here may be able to offer more specific suggestions. Folks...

Choosing paper size
How do I put in the size paper I want for my printer in Excel 2000 with Windows xp? Our old computer had Windows 98. We transferred files from 1 hard drive to the other, but the green bar paper 14 1/2 x 11 is not an option. is there a way of putting in the paper size you want? What type of printer? (make, model) Dan E "Vicki Lenz" <bruce28@charter.net> wrote in message news:018401c35142$49ef0b40$a301280a@phx.gbl... > How do I put in the size paper I want for my > printer in Excel 2000 with Windows xp? Our > old computer had Windows 98. We transferred > file...

Outlook 2003 custom form fields not accessible to Exchange Server
Hi, I have created a very simple custom form which is published to the Organisational Library so users can send simple SMS messages through our EMail system. I don't seem to have any ability to change the properties of the standard Outlook Message field so I created a very simple text object with a maximum length and simple validation. However my Exchange 'expert' tells me that he cannot process any content in an Outlook form unless it is in the Message field! :-( Is this the case? Does Exchange have no means of opening a published custom form and extracting the data in a user...

Deleting Customer Records
We are attempting to clean up our data and want to remove some old customer master records that were imported from our old accounting system. When we try to delete the customer, we are not allowed because GP says there are transactions that exist for this customer. We're sure we have not transacted against this customer record in GP, and a customer transaction inquiry also shows no transactions. Is there any way to determine why we are not being allowed to delete these customers? Perhaps there is a field somewhere populated that can be modified to allow customer deletio...

1st Payment in Debt Reduction Planner Always Wrong
I'm having an issue with the Debt Reduction planner. This happened in Money 2006, and now in 2008 as well. I have a credit card that I want to put into the debt reduction planner. I enter it, including all of the required information such as interest rate, credit line, next payment date, etc... The debt reduction plan looks right, except the 1st payment is almays $10,000+. For example, I have $16,110 in the account. I tell the debt reduction planner to pay $400 a month with no "one time" payment, $0 estimated spending, and payment frequency monthly. When I see the payment s...

Record (row) count by Table in Database
I have a rather critical need to monitor the number of rows for each table in a database. The Database contains 17 tables, and at any given time any one of the tables can contain zero (0) rows or several thousand rows. I was thinking along the lines of creating another table which would contain: Table Name, Date/Time and the Row Count. and appending the row count for each table via a summary query. This way, I could see not only the number of rows in each table at a given point of time but also look at the 'trend' by table. I have two problems with this approach, the f...

Restricting access to activities for given customized entity
Hi everyone, I have a scenario with a company that would like to have any activities associated with account and contacts to be open to every user (Organisation Read Access) as a base line but for given projects associated with given accounts and contacts, activites should be confidential and access level restricted to user or team (User Read Access). When project is finished it can be shared with everyone (i.e. share to team "All users"). I can create custom entity called Project with relationship to Account and restrict access to this Project entity on a user level. From entity...

Popup messages for a specific customer
Hi All, My client would like to have pop-up messages for the sales clerks on spcific customers. Do any one know of a ISV with this type of product for rms? -- Crystal Roach If you mean display custom text fields for customers you can do that by modifying the status window HTML in POS. If you want a ready-made version of this we offer DRS Status Window. Info at: http://www.digitalretailer.com/AddinPDFs/StatusWindowPDF.pdf "Crystal Clear" <CrystalClear@discussions.microsoft.com> wrote in message news:92F8C02B-86C9-4338-8D29-ED0028A558C3@microsoft.com... > Hi All, > ...

choose function
I would like to use choose function from dropdown box column 4. for example: Choose([myform[![mydropdown]![column](4), myvalue = "something", myvalue = "that thing") Can I do above using choose function? If I can, can you please advise the proper syntax? If not, any other solution for this? Your help is great appreciated, On Wed, 19 May 2010 07:37:48 -0700 (PDT), iccsi <inungh@gmail.com> wrote: Choose(Me.myDropdown.Column(4), "TextForIndex0", "TextForIndex1", "TextForIndex2, ...etc) -Tom. Microsoft Access MVP >...

Accessing Custom entities using webservice
Can anyone tell me if it is possible to access custom entities using a webservice. I am trying to create an instance of a custom entity that has already been established in the CRM. Thanks. ...

Sales Invoice History
At what does the sales invoice move from open to history?? We have posted several invoices but when I run the Sales History Reports nothing is there. ...

Updating contact line after invoice
I need to be able to update a field for a contract line programmatically based on a case resolution. The problem is, it seems that in order to create a case for a contract, the contract must be invoiced. But once the contract is invoiced it seems that I am unable to make any updates to the contract's details via the API. It says: SOAP Server Application Faulted 80043203The state of the contract is invalid.The state is invalid, this contract line item cannot be updated Any ideas as to how I can edit the contract line after invoicing? Thanks. -Guenther ...