Percentage and subtraction

  • Thread starter Thread starter Love Buzz
  • Start date Start date
L

Love Buzz

Hello all.

Here is what I am trying to do. I want to compare the count of RR Code 1 to
RR Code 2 in my query. That is working fine except it's only reflecting
results when the Customer Name has both RR Code 1 and RR Code 2.

What I want is it to reflect a formula that includes RR Code 1 even if there
are no values in RR Code 2 for that same account. I hope that made sense.

Here is the SQL


SELECT [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit Analysis:
RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code 1].[CountOfRR
Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code], ([Redeposit
Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR Code 2.CountOfRR
Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code] AS Expr1
FROM [Redeposit Analysis: RR Code 2] INNER JOIN [Redeposit Analysis: RR Code
1] ON [Redeposit Analysis: RR Code 2].[Depositing Account] = [Redeposit
Analysis: RR Code 1].[Depositing Account]
GROUP BY [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit
Analysis: RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code
1].[CountOfRR Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code],
([Redeposit Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR Code
2.CountOfRR Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code];
 
Double-click the line joining the 2 tables in the upper pane of query
design. Access pops up a dialog offering 3 options. Choose the one that
says:
All records From [RR Code 1], and any matches from [RR Code 2].

Technically, this is called an outer join. More info in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html
 
Perfect...thank you.

Another question. I am comparing one queury to another and I need to
reflect RR Code 2 as zero when the field is blank. I am sure that too is an
easy fix, but I am lost and getting frustrated.

Thanks so much for your help.

Allen Browne said:
Double-click the line joining the 2 tables in the upper pane of query
design. Access pops up a dialog offering 3 options. Choose the one that
says:
All records From [RR Code 1], and any matches from [RR Code 2].

Technically, this is called an outer join. More info in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Love Buzz said:
Hello all.

Here is what I am trying to do. I want to compare the count of RR Code 1
to
RR Code 2 in my query. That is working fine except it's only reflecting
results when the Customer Name has both RR Code 1 and RR Code 2.

What I want is it to reflect a formula that includes RR Code 1 even if
there
are no values in RR Code 2 for that same account. I hope that made sense.

Here is the SQL


SELECT [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit
Analysis:
RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code
1].[CountOfRR
Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code], ([Redeposit
Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR Code
2.CountOfRR
Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code] AS Expr1
FROM [Redeposit Analysis: RR Code 2] INNER JOIN [Redeposit Analysis: RR
Code
1] ON [Redeposit Analysis: RR Code 2].[Depositing Account] = [Redeposit
Analysis: RR Code 1].[Depositing Account]
GROUP BY [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit
Analysis: RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code
1].[CountOfRR Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code],
([Redeposit Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR
Code
2.CountOfRR Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code];
Click to expand...
Click to expand...
 
In query design view, using the outer join query, enter an expression like
this into a fresh column in the Field row:
IIF([RR Code 2] Is Null, 0, [RR Code 2])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Love Buzz said:
Perfect...thank you.

Another question. I am comparing one queury to another and I need to
reflect RR Code 2 as zero when the field is blank. I am sure that too is
an
easy fix, but I am lost and getting frustrated.

Thanks so much for your help.

Allen Browne said:
Double-click the line joining the 2 tables in the upper pane of query
design. Access pops up a dialog offering 3 options. Choose the one that
says:
All records From [RR Code 1], and any matches from [RR Code 2].

Technically, this is called an outer join. More info in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Love Buzz said:
Hello all.

Here is what I am trying to do. I want to compare the count of RR Code
1
to
RR Code 2 in my query. That is working fine except it's only
reflecting
results when the Customer Name has both RR Code 1 and RR Code 2.

What I want is it to reflect a formula that includes RR Code 1 even if
there
are no values in RR Code 2 for that same account. I hope that made
sense.

Here is the SQL


SELECT [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit
Analysis:
RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code
1].[CountOfRR
Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code], ([Redeposit
Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR Code
2.CountOfRR
Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code] AS Expr1
FROM [Redeposit Analysis: RR Code 2] INNER JOIN [Redeposit Analysis: RR
Code
1] ON [Redeposit Analysis: RR Code 2].[Depositing Account] = [Redeposit
Analysis: RR Code 1].[Depositing Account]
GROUP BY [Redeposit Analysis: RR Code 1].[Customer Name], [Redeposit
Analysis: RR Code 1].[Depositing Account], [Redeposit Analysis: RR Code
1].[CountOfRR Code], [Redeposit Analysis: RR Code 2].[CountOfRR Code],
([Redeposit Analysis: RR Code 1.CountOfRR Code]-[Redeposit Analysis: RR
Code
2.CountOfRR Code])/[Redeposit Analysis: RR Code 1.CountOfRR Code];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top