Return repeats info in "8s"

[Joy Rose]

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 Snell [MVP]]

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>

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

 

[Joy Rose]

I tried you SQL statement and got message that there was an error in the
FROM statement.

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>

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 Snell [MVP]]

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>

I tried you SQL statement and got message that there was an error in the
FROM statement.

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>

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

 

[Joy Rose]

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.

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.

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>

I tried you SQL statement and got message that there was an error in the
FROM statement.

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>



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 Snell [MVP]]

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>

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.

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.

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...

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>



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

 

[Joy Rose]

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.

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>

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.

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.

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>



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 Snell [MVP]]

Post the SQL of that query, too, please.

--

Ken Snell
<MS ACCESS MVP>

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.

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?

Address,City,StateorProvince,PostalCode,Country,ContactTitle,PhoneNumber,Fax

in
the

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>



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

 

[Joy Rose]

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.

Post the SQL of that query, too, please.

--

Ken Snell
<MS ACCESS MVP>

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.

records

in

Address,City,StateorProvince,PostalCode,Country,ContactTitle,PhoneNumber,Fax when

I

error

in

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>



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 Snell [MVP]]

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!

 

[Joy Rose]

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.

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>

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]]

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>

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.

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!

 

[Joy Rose]

When I enter this I get no data.

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>

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.

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

 

[Joy Rose]

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

When I enter this I get no data.

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>

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.
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

or
by

that

I

 

[Ken Snell [MVP]]

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>

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

When I enter this I get no data.
to
not

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>


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.
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 company

or Then
we that