Return repeats info in "8s"

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]" wrote in message
...
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,Co ntactTitle,PhoneNumber,Fax
Number,Notes,Email.

Table: Receivables Aging Report Query --
ShipDate,CompanyName,FreightCharge,SalesTaxRate,Li neTotal,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" wrote in message
...
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.

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" wrote in message
. ..
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]" wrote in message
...
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,Co ntactTitle,PhoneNumber,Fax
Number,Notes,Email.

Table: Receivables Aging Report Query --
ShipDate,CompanyName,FreightCharge,SalesTaxRate,Li neTotal,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" wrote in message
...
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.

When I enter this I get no data.
"Ken Snell [MVP]" wrote in message
...
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" wrote in message
. ..
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]" wrote in message
...
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,Co ntactTitle,PhoneNumber,Fax
Number,Notes,Email.

Table: Receivables Aging Report Query --
ShipDate,CompanyName,FreightCharge,SalesTaxRate,Li neTotal,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" wrote in message
...
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.

I created this new query and I still get no data.

SELECT DISTINCTROW Customers.CompanyName,
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, Orders.ShipDate
FROM (Customers INNER JOIN (Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID = [Sum Of Payments Query].OrderID) ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Orders.ShipDate) Is Not Null))
GROUP BY Customers.CompanyName, Orders.ShipDate
HAVING
(((Sum(IIf((Date()-[ShipDate])31,[LineTotal]*(1+[SalesTaxRate])+[FreightCha
rge]-nz([Total Payments]),0)))0));

"Joy Rose" wrote in message
news
When I enter this I get no data.
"Ken Snell [MVP]" wrote in message
...
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" wrote in message
. ..
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]" wrote in message
...
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,Co ntactTitle,PhoneNumber,Fax
Number,Notes,Email.

Table: Receivables Aging Report Query --
ShipDate,CompanyName,FreightCharge,SalesTaxRate,Li neTotal,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" wrote in message
...
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.

Getting no records means either that the joined query/tables are not
returning any records or your WHERE statement is filtering out records or
your HAVING clause is filtering out records.

Try removing the WHERE and HAVING portions of the query and see what you
get. We're doing troubleshooting here so that we can track down where
something is not right.

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
. ..
I created this new query and I still get no data.

SELECT DISTINCTROW Customers.CompanyName,

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, Orders.ShipDate
FROM (Customers INNER JOIN (Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID = [Sum Of Payments Query].OrderID) ON Customers.CustomerID
=
Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE (((Orders.ShipDate) Is Not Null))
GROUP BY Customers.CompanyName, Orders.ShipDate
HAVING

(((Sum(IIf((Date()-[ShipDate])31,[LineTotal]*(1+[SalesTaxRate])+[FreightCha
rge]-nz([Total Payments]),0)))0));

"Joy Rose" wrote in message
news
When I enter this I get no data.
"Ken Snell [MVP]" wrote in message
...
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" wrote in message
. ..
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]" wrote in
message
...
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,Co ntactTitle,PhoneNumber,Fax
Number,Notes,Email.

Table: Receivables Aging Report Query --
ShipDate,CompanyName,FreightCharge,SalesTaxRate,Li neTotal,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" wrote in message
...
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.