Re: Return repeats info in "8s"
From: Joy Rose (joyrose_at_bellsouth.net)
Date: 10/13/04
- Next message: Ken Snell [MVP]: "Re: Calculated Mean"
- Previous message: Ken Snell [MVP]: "Re: Calculated Mean"
- In reply to: Ken Snell [MVP]: "Re: Return repeats info in "8s""
- Next in thread: Joy Rose: "Re: Return repeats info in "8s""
- Reply: Joy Rose: "Re: Return repeats info in "8s""
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 13 Oct 2004 09:56:58 -0600
When I enter this I get no data.
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:uT8qtHNsEHA.2320@TK2MSFTNGP12.phx.gbl...
> You're trying to use a query as a table within itself? In other words, the
> query is referencing itself as one of its source tables? That is not
> possible.
>
> We're not making progress in this direction. I apologize that I seem to
not
> be grasping the setup.
>
> Let me take a stab at a change to your original query and see if this
> eliminates the octiplated records (then we'll go from there):
>
> SELECT DISTINCTROW
>
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
> ]-nz([Total Payments]),0)) AS [Current], Sum(IIf((Date()-[ShipDate])<61
And
>
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
> tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
>
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
> tal Payments]),0)) AS [61-90 Days],
>
Sum(IIf((Date()-[ShipDate])>91,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
> ]-nz([Total Payments]),0)) AS [91+Days],
> Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]))
AS
> Balance, [Receivables Aging Report Query].CompanyName
> FROM [Receivables Aging Report Query]
> WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
> GROUP BY [Receivables Aging Report Query].CompanyName
> HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
> Payments])))>0));
>
> Does this return one record for each desired result?
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "Joy Rose" <joyrose@bellsouth.net> wrote in message
> news:nHWad.90531$DV3.87661@bignews5.bellsouth.net...
> > Ken -- the original query as you listed below is the AR query. I
> displayed
> > the query in SQL view which is what you are seeing below. The Table:
> > Customers is a good table. The Table: Receivables Aging Report Query
> came
> > from going to relationships and showing all tables as well as the
query --
> > which is the Table: Receivables Aging Report.
> >
> > My attempt is to list total sales by customer by date.
> >
> > Thank you.
> > "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> > news:%23EV6INAsEHA.1816@TK2MSFTNGP15.phx.gbl...
> > > I admit - I am confused.
> > >
> > > The original query that you posted (first message in the thread)
> > references
> > > Receivables Aging Report Query as a "table" or "query" that the
original
> > > query is using as a data source. I've reposted that query below:
> > >
> > > SELECT DISTINCTROW
> > >
> >
>
Sum(IIf((Date()-[ShipDate])<31,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
> > > ]-nz([Total Payments]),0)) AS [Current],
Sum(IIf((Date()-[ShipDate])<61
> > And
> > >
> >
>
(Date()-[ShipDate]>30),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
> > > tal Payments]),0)) AS [31-60 Days], Sum(IIf((Date()-[ShipDate])<91 And
> > >
> >
>
(Date()-[ShipDate]>60),[LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([To
> > > tal Payments]),0)) AS [61-90 Days],
> > >
> >
>
Sum(IIf((Date()-[ShipDate])>91,[LineTotal]*(1+[SalesTaxRate])+[FreightCharge
> > > ]-nz([Total Payments]),0)) AS [91+Days],
> > > Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
Payments]))
> > AS
> > > Balance, [Receivables Aging Report Query].CompanyName,
> > Customers.[Account#]
> > > FROM [Receivables Aging Report Query], Customers
> > > WHERE ((([Receivables Aging Report Query].ShipDate) Is Not Null))
> > > GROUP BY [Receivables Aging Report Query].CompanyName,
> > Customers.[Account#]
> > > HAVING (((Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total
> > > Payments])))>0));
> > >
> > >
> > > What I'm now understanding is that this original query is the
> Receivables
> > > Aging Report Query query? That can't be a correct understanding.
> > >
> > > As I noted in my first reply, I'm sure that the duplicated
(octiplated?)
> > > records are because your tables are not joined correctly, and you're
> > picking
> > > up a cartesian query result.
> > >
> > > However, to try to help, I'm trying to understand the structure of the
> > > Customers table (which you've posted) and the Receivables Aging Report
> > Query
> > > (which you seem to say is the original query). I've reproduced that
info
> > > here too:
> > >
> > > Table: Customers -- CustomerID, Account#, CompanyName,
> ContactFirstName,
> > > ContactLastName,Billing
> > >
> >
>
Address,City,StateorProvince,PostalCode,Country,ContactTitle,PhoneNumber,Fax
> > > Number,Notes,Email.
> > >
> > > Table: Receivables Aging Report Query --
> > > ShipDate,CompanyName,FreightCharge,SalesTaxRate,LineTotal,Total
> Payments.
> > >
> > >
> > > Is the final query intended to show the aged receivables by company or
> by
> > > customer? That will dicate how the join needs to be done.
> > >
> > > Let's clarify what the exact SQL statement is for the Receivables
Aging
> > > Report Query so that I can more properly see what is happening. Then
we
> > > should be able to straighten things out... I think!
> > >
> > > --
> > >
> > > Ken Snell
> > > <MS ACCESS MVP>
> > >
> > >
> > > "Joy Rose" <joyrose@bellsouth.net> wrote in message
> > > news:M2Had.74613$yp.7068@bignews1.bellsouth.net...
> > > > Ken -- sorry that I am not explaining myself clearly. The SQL that
I
> > > listed
> > > > originally is for the Accounts Receivable Query. Thank you for
your
> > > > assistance.
> > >
> > >
> >
> >
>
>
- Next message: Ken Snell [MVP]: "Re: Calculated Mean"
- Previous message: Ken Snell [MVP]: "Re: Calculated Mean"
- In reply to: Ken Snell [MVP]: "Re: Return repeats info in "8s""
- Next in thread: Joy Rose: "Re: Return repeats info in "8s""
- Reply: Joy Rose: "Re: Return repeats info in "8s""
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
