Query to get sum of a field if two fields match

[Guest]

Hi,
Im trying to get a query to give me the sum of a field, if two other fields
in the table match.
for eg (table):
Reporting Month Closure Month Actual balance
Sep 07 Sep 07 1
Sep 07 Sep 07 1
Sep 07 Oct 07 1
In the above table, i need to build a query to get me the sum of the actual
balance if the reporting month and the closure month match.
The input format for the reporting month and the closure month is dd-mmm-yy,
however the display format is mmm-yy

 

[Guest]

SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE [Reporting Month]-[Closure Month])=0;

The SQL above assumes that the Reporting Month and Closure Month fields are
actual Date/Time data types AND that the date stored is the same. In other
words, no 1 Sep 07 and 2 Sep 07 combinations.

 

[Guest]

There just might be a day of the month that is different. I would recommend
something like this --
SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE Format([Reporting Month],"yyyymm")=Format([Closure Month],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE [Reporting Month]-[Closure Month])=0;

The SQL above assumes that the Reporting Month and Closure Month fields are
actual Date/Time data types AND that the date stored is the same. In other
words, no 1 Sep 07 and 2 Sep 07 combinations.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,
Im trying to get a query to give me the sum of a field, if two other fields
in the table match.
for eg (table):
Reporting Month Closure Month Actual balance
Sep 07 Sep 07 1
Sep 07 Sep 07 1
Sep 07 Oct 07 1
In the above table, i need to build a query to get me the sum of the actual
balance if the reporting month and the closure month match.
The input format for the reporting month and the closure month is dd-mmm-yy,
however the display format is mmm-yy

 

[Guest]

Hey..
Thank you very much folks.

There just might be a day of the month that is different. I would recommend
something like this --
SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE Format([Reporting Month],"yyyymm")=Format([Closure Month],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE [Reporting Month]-[Closure Month])=0;

The SQL above assumes that the Reporting Month and Closure Month fields are
actual Date/Time data types AND that the date stored is the same. In other
words, no 1 Sep 07 and 2 Sep 07 combinations.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,
Im trying to get a query to give me the sum of a field, if two other fields
in the table match.
for eg (table):
Reporting Month Closure Month Actual balance
Sep 07 Sep 07 1
Sep 07 Sep 07 1
Sep 07 Oct 07 1
In the above table, i need to build a query to get me the sum of the actual
balance if the reporting month and the closure month match.
The input format for the reporting month and the closure month is dd-mmm-yy,
however the display format is mmm-yy

 

[Guest]

I have another issue, please help:
I need to import data from an excel sheet to a table in access. The excel
sheet may contain records that are already in the database. While importing,
the records that are already in the database must not be imported.
My database has two columns - Card number & Status
While importing, access must look for a match in the card number column, if
the match exists, then a second comparison must be done with the Status
column. If the status column also matches, then the record must not be
imported.
However, if the card number matches and the status does not match, then the
record must be imported.
Records wherein the card number does not match must also be imported.

Scenario (excel sheet)
Card number Status
1 Closed
2 Open

Table (Access)
Card number Status
1 Closed
2 Closed
From the above example, when importing only the record for card number 2
must be imported.
I am not good at coding.

There just might be a day of the month that is different. I would recommend
something like this --
SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE Format([Reporting Month],"yyyymm")=Format([Closure Month],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE [Reporting Month]-[Closure Month])=0;

The SQL above assumes that the Reporting Month and Closure Month fields are
actual Date/Time data types AND that the date stored is the same. In other
words, no 1 Sep 07 and 2 Sep 07 combinations.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,
Im trying to get a query to give me the sum of a field, if two other fields
in the table match.
for eg (table):
Reporting Month Closure Month Actual balance
Sep 07 Sep 07 1
Sep 07 Sep 07 1
Sep 07 Oct 07 1
In the above table, i need to build a query to get me the sum of the actual
balance if the reporting month and the closure month match.
The input format for the reporting month and the closure month is dd-mmm-yy,
however the display format is mmm-yy

 

[Guest]

Make the combination of the Card number & Status fields a Unique Index or
even the Primary Key. Access will not allow a duplicate record to append to
the table. You may need to clean up exisiting data if you get an error while
creating the unique index.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have another issue, please help:
I need to import data from an excel sheet to a table in access. The excel
sheet may contain records that are already in the database. While importing,
the records that are already in the database must not be imported.
My database has two columns - Card number & Status
While importing, access must look for a match in the card number column, if
the match exists, then a second comparison must be done with the Status
column. If the status column also matches, then the record must not be
imported.
However, if the card number matches and the status does not match, then the
record must be imported.
Records wherein the card number does not match must also be imported.

Scenario (excel sheet)
Card number Status
1 Closed
2 Open

Table (Access)
Card number Status
1 Closed
2 Closed
From the above example, when importing only the record for card number 2
must be imported.
I am not good at coding.

There just might be a day of the month that is different. I would recommend
something like this --
SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE Format([Reporting Month],"yyyymm")=Format([Closure Month],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

SELECT Sum(Sashi.[Actual Balance]) AS SumOfActualBalance
FROM Sashi
WHERE [Reporting Month]-[Closure Month])=0;

The SQL above assumes that the Reporting Month and Closure Month fields are
actual Date/Time data types AND that the date stored is the same. In other
words, no 1 Sep 07 and 2 Sep 07 combinations.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi,
Im trying to get a query to give me the sum of a field, if two other fields
in the table match.
for eg (table):
Reporting Month Closure Month Actual balance
Sep 07 Sep 07 1
Sep 07 Sep 07 1
Sep 07 Oct 07 1
In the above table, i need to build a query to get me the sum of the actual
balance if the reporting month and the closure month match.
The input format for the reporting month and the closure month is dd-mmm-yy,
however the display format is mmm-yy