Return repeats info in "8s"

I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the same
information with the exception of the control #. The lists 8 different
numbers and then starts again with the first through eighth and again first
through eight, etc.

HELP

You're not doing an outer join between your two tables...instead, you're
doing a cartesian join, so you'll get repetitions of each returned record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName = Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the same
information with the exception of the control #. The lists 8 different
numbers and then starts again with the first through eighth and again
first
through eight, etc.

HELP

I tried you SQL statement and got message that there was an error in the
FROM statement.
"Ken Snell [MVP]" wrote in message
...
You're not doing an outer join between your two tables...instead, you're
doing a cartesian join, so you'll get repetitions of each returned record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName = Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the same
information with the exception of the control #. The lists 8 different
numbers and then starts again with the first through eighth and again
first
through eight, etc.

HELP

Is there a CustomerNumber field in both tables? I just guessed when I posted
that suggestion. If there is a different field that links the two tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an error in the
FROM statement.
"Ken Snell [MVP]" wrote in message
...
You're not doing an outer join between your two tables...instead, you're
doing a cartesian join, so you'll get repetitions of each returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the same
information with the exception of the control #. The lists 8
different
numbers and then starts again with the first through eighth and again
first
through eight, etc.

HELP

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.

I have right joined the tables with CompanyName. I am still getting "sets
of 8". The table Receivables Aging Report Query did not exist until I
created the query and report. Is this the problem.


"Ken Snell [MVP]" wrote in message
...
Is there a CustomerNumber field in both tables? I just guessed when I
posted
that suggestion. If there is a different field that links the two tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an error in the
FROM statement.
"Ken Snell [MVP]" wrote in message
...
You're not doing an outer join between your two tables...instead,
you're
doing a cartesian join, so you'll get repetitions of each returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the same
information with the exception of the control #. The lists 8
different
numbers and then starts again with the first through eighth and
again
first
through eight, etc.

HELP

What query did you use to create the Receivables Aging Report Query table?
Did you look at that table to see if it has multiply duplicated records in
it?

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
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.

I have right joined the tables with CompanyName. I am still getting "sets
of 8". The table Receivables Aging Report Query did not exist until I
created the query and report. Is this the problem.


"Ken Snell [MVP]" wrote in message
...
Is there a CustomerNumber field in both tables? I just guessed when I
posted
that suggestion. If there is a different field that links the two
tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an error in
the
FROM statement.
"Ken Snell [MVP]" wrote in message
...
You're not doing an outer join between your two tables...instead,
you're
doing a cartesian join, so you'll get repetitions of each returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try
this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the
same
information with the exception of the control #. The lists 8
different
numbers and then starts again with the first through eighth and
again
first
through eight, etc.

HELP

I am incorrect. When I look at relationships, there is the option to view
tables, queries or both. I linked the "query" table to the customer table.
Under the tab table, there is no Receivables Aging Report Query table.
"Ken Snell [MVP]" wrote in message
...
What query did you use to create the Receivables Aging Report Query table?
Did you look at that table to see if it has multiply duplicated records in
it?

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
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.

I have right joined the tables with CompanyName. I am still getting
"sets
of 8". The table Receivables Aging Report Query did not exist until I
created the query and report. Is this the problem.


"Ken Snell [MVP]" wrote in message
...
Is there a CustomerNumber field in both tables? I just guessed when I
posted
that suggestion. If there is a different field that links the two
tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an error in
the
FROM statement.
"Ken Snell [MVP]" wrote in
message
...
You're not doing an outer join between your two tables...instead,
you're
doing a cartesian join, so you'll get repetitions of each returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try
this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the
same
information with the exception of the control #. The lists 8
different
numbers and then starts again with the first through eighth and
again
first
through eight, etc.

HELP

Post the SQL of that query, too, please.

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
. ..

I am incorrect. When I look at relationships, there is the option to view
tables, queries or both. I linked the "query" table to the customer
table.
Under the tab table, there is no Receivables Aging Report Query table.
"Ken Snell [MVP]" wrote in message
...
What query did you use to create the Receivables Aging Report Query
table?
Did you look at that table to see if it has multiply duplicated records
in
it?

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
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.

I have right joined the tables with CompanyName. I am still getting
"sets
of 8". The table Receivables Aging Report Query did not exist until I
created the query and report. Is this the problem.


"Ken Snell [MVP]" wrote in message
...
Is there a CustomerNumber field in both tables? I just guessed when
I
posted
that suggestion. If there is a different field that links the two
tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an error
in
the
FROM statement.
"Ken Snell [MVP]" wrote in
message
...
You're not doing an outer join between your two
tables...instead,
you're
doing a cartesian join, so you'll get repetitions of each
returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field, try
this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with the
same
information with the exception of the control #. The lists 8
different
numbers and then starts again with the first through eighth
and
again
first
through eight, etc.

HELP

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.
"Ken Snell [MVP]" wrote in message
...
Post the SQL of that query, too, please.

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
. ..

I am incorrect. When I look at relationships, there is the option to
view
tables, queries or both. I linked the "query" table to the customer
table.
Under the tab table, there is no Receivables Aging Report Query table.
"Ken Snell [MVP]" wrote in message
...
What query did you use to create the Receivables Aging Report Query
table?
Did you look at that table to see if it has multiply duplicated
records
in
it?

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
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.

I have right joined the tables with CompanyName. I am still getting
"sets
of 8". The table Receivables Aging Report Query did not exist until
I
created the query and report. Is this the problem.


"Ken Snell [MVP]" wrote in
message
...
Is there a CustomerNumber field in both tables? I just guessed
when
I
posted
that suggestion. If there is a different field that links the two
tables,
then you'll want to use that field.

Otherwise, give us more info about the table structures...

--

Ken Snell
MS ACCESS MVP

"Joy Rose" wrote in message
.. .
I tried you SQL statement and got message that there was an
error
in
the
FROM statement.
"Ken Snell [MVP]" wrote in
message
...
You're not doing an outer join between your two
tables...instead,
you're
doing a cartesian join, so you'll get repetitions of each
returned
record
based on how many total records are in the other table.

Assuming that you have CompanyName field as a joining field,
try
this:

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] RIGHT JOIN Customers
ON [Receivables Aging Report Query].CustomerName =
Customers.CustomerName
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));
--

Ken Snell
MS ACCESS MVP



"Joy Rose" wrote in message
.. .
I have the following Accounts Receivable Aging query.

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));

When the report prints, all fields are listed 8 times with
the
same
information with the exception of the control #. The lists
8
different
numbers and then starts again with the first through eighth
and
again
first
through eight, etc.

HELP

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.