checking data in two rows and change the data accordingly

[Guest]

I am trying to incorporate an excel formula into Access. The formula looks
like this:
'=IF(AND(Ab4=Ab5,Ad4=Ad5),"Piggyback","Standalone")
How can I make a query in Access to check the data in consecutive rows.
Thanks.
Purnima sharma

 

[Duane Hookom]

There is no such thing as "consecutive rows" in an Access table. I'm sure
someone can help you if you provide actual table and field names, sample
records, and desired results.

 

[OfficeDev18 via AccessMonster.com]

Hi, Purnima,

Duane is right; we need more information to give you explicit guidance.

Having said that, however, I'll tell you that what you need to do is to
determine what fields (columns) in row 4 and row 5 are identical, so in an
Access totals query, those fields can be Grouped By. If there are no
similarities, you wouldn't have the question. Once you identify these group-
able fields, you would then use the Count aggregate function on the fields
represented by columns ab and ad. If the Count results in a number more than
1, then you know it's a "piggyback", otherwise it's a "standalone". Just make
sure your data is properly sorted so the proper row is first and the proper
row is last.

You should be able to make a go of it with this information.

HTH

 

[Guest]

Hi Duane,
I posted the question two times. Somehow when I looked for my question, it
didn't show in the database. However, it appeared next time when I opened
it. I am sorry for that and thank you for the response.
I work for a mortgage company and I need to change an Excel database to
Access database. I have to make a query in which I have to make a new column
called "Type of loan", and put an expression which will lookup in the table
and if there are duplicate records for Social Security Number and Address in
the table, then in Column "Type of loan", first entry should be "standalone"
and the second should be "Piggyback". In the excel table, it checks for the
rows consecutively. But I think it is ok if they are not. I will appreciate
if you can help me.

 

[Guest]

You will need some more information than you have mention like a loan date or
document number. Loan date is best.

Make a backup of the database. Create a TOTALS make table query pulling SSN,
Loan Date, and document number. Select First in the Group By row of the Loan
Date column. Run query.

Use that table you just made to update those records selected for the table.

Create another select query using the made table joined to orignal table to
check if there are more records without loan type. Update those to
"Piggyback"

 

[Guest]

Dear Sam,
You are probably right. However, my question is how will you incorporate it
in IF formula. Is there anything in Access like Ifduplicate(column name) or
something similar to it? If there is, could you please tell me the syntax.
Thanks.
Purnima

 

[OfficeDev18 via AccessMonster.com]

Hi, Purnima,

You would say in VBA,

If (CountOfFieldAB>1) or (CountOfFieldAD>1) Then

something

End If

Your actual query in SQL-ese would read Count(FieldAB) As CountOfFieldAB ....
Count(FieldAD) As CountOfFieldAD ...... WHERE ...... Count(FieldAB) > 1 ......
.. Count(FieldAD) > 1 .....

Of course, you would have to fill in the rest of the blanks.

HTH

Dear Sam,
You are probably right. However, my question is how will you incorporate it
in IF formula. Is there anything in Access like Ifduplicate(column name) or
something similar to it? If there is, could you please tell me the syntax.
Thanks.
Purnima

Hi, Purnima,

[quoted text clipped - 24 lines]

 

[Guest]

Dear sam,

I tried to incorporate count formula in my query, but I get an error
message. My code is a s follows:

piggybacks1:
Select(IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback"))
from [Pipeline detail]
group by [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address]
Having (((Count([ [Pipeline Detail].[Borrower's SSN] ) )>1) and ((count(
[Pipeline Detail].[Property Address]) )>1))

Could you please correct my query. I think I am not coding it correctly.
Please help me. Thanks.

Purnima

 

[OfficeDev18 via AccessMonster.com]

Hi, Purnima,

If you want the query to pick up both scenarios, you have to ditch the HAVING
..... >1 clause. Try this (I put it into Access' query designer):

SELECT (IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback")) AS Expr1, [Pipeline
Detail].[Borrower's SSN], [Pipeline Detail].[Property Address], Count([
[Pipeline Detail].[Borrower's SSN]), Count([Pipeline Detail].[Property
Address])
FROM [Pipeline detail]
GROUP BY [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address];

Out of curiosity, what is your error message?

HTH

Dear sam,

I tried to incorporate count formula in my query, but I get an error
message. My code is a s follows:

piggybacks1:
Select(IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback"))
from [Pipeline detail]
group by [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address]
Having (((Count([ [Pipeline Detail].[Borrower's SSN] ) )>1) and ((count(
[Pipeline Detail].[Property Address]) )>1))

Could you please correct my query. I think I am not coding it correctly.
Please help me. Thanks.

Purnima

Hi, Purnima,

[quoted text clipped - 24 lines]

 

[Guest]

Hi Sam,
The error message I am getting is:
Close the subquery's syntax and enclose the subquery in parenthesis

I am trying to use your query, but still not successful. I will let you know
once I am able to make it work. Thanks for your help. I really appreciate it.

Hi, Purnima,

If you want the query to pick up both scenarios, you have to ditch the HAVING
..... >1 clause. Try this (I put it into Access' query designer):

SELECT (IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback")) AS Expr1, [Pipeline
Detail].[Borrower's SSN], [Pipeline Detail].[Property Address], Count([
[Pipeline Detail].[Borrower's SSN]), Count([Pipeline Detail].[Property
Address])
FROM [Pipeline detail]
GROUP BY [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address];

Out of curiosity, what is your error message?

HTH

Dear sam,

I tried to incorporate count formula in my query, but I get an error
message. My code is a s follows:

piggybacks1:
Select(IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback"))
from [Pipeline detail]
group by [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address]
Having (((Count([ [Pipeline Detail].[Borrower's SSN] ) )>1) and ((count(
[Pipeline Detail].[Property Address]) )>1))

Could you please correct my query. I think I am not coding it correctly.
Please help me. Thanks.

Purnima

Hi, Purnima,

[quoted text clipped - 24 lines]

Thanks.
Purnima sharma

 

[Guest]

Hi Sam,
The code still doesn't work. It keeps on giving error that subquery must be
enclosed in parenthesis. I am not using a sub query, then why is it giving
that message? Thanks.
Purnima

Hi, Purnima,

If you want the query to pick up both scenarios, you have to ditch the HAVING
..... >1 clause. Try this (I put it into Access' query designer):

SELECT (IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback")) AS Expr1, [Pipeline
Detail].[Borrower's SSN], [Pipeline Detail].[Property Address], Count([
[Pipeline Detail].[Borrower's SSN]), Count([Pipeline Detail].[Property
Address])
FROM [Pipeline detail]
GROUP BY [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address];

Out of curiosity, what is your error message?

HTH

Dear sam,

I tried to incorporate count formula in my query, but I get an error
message. My code is a s follows:

piggybacks1:
Select(IIf(Count([Pipeline Detail]![Borrower's SSN])=1 And Count([Pipeline
Detail]![Property Address])=1,"Standalone","Piggyback"))
from [Pipeline detail]
group by [Pipeline Detail].[Borrower's SSN], [Pipeline Detail].[Property
Address]
Having (((Count([ [Pipeline Detail].[Borrower's SSN] ) )>1) and ((count(
[Pipeline Detail].[Property Address]) )>1))

Could you please correct my query. I think I am not coding it correctly.
Please help me. Thanks.

Purnima

Hi, Purnima,

[quoted text clipped - 24 lines]

Thanks.
Purnima sharma

 

[OfficeDev18 via AccessMonster.com]

Hi, Purnima,

It's hard to know exactly without visualizing it. However, I suspect that
since this is only a small part of an overall query, it really is a subquery.
Try putting the entire SELECT ... ; statement in parentheses and see if that
makes Access happy.

In general, if a SELECT statement appears either on the top line or the
"Criteria" line of the query designer it is automatically a subquery and
needs parentheses.

HTH

Hi Sam,
The code still doesn't work. It keeps on giving error that subquery must be
enclosed in parenthesis. I am not using a sub query, then why is it giving
that message? Thanks.
Purnima

Hi, Purnima,

[quoted text clipped - 38 lines]