Can't get the syntax down...

[Jenay]

Sorry... I don't have the syntax down for Access/SQL, can you help me?
I'm trying to build a query; I have a table that has:
Debit
Credit
CustName
TransDate

and I want to build a new query where I have
CustName
date30

where CustName is the same from the table
and where date30 is the sum of all the debit - credits for each CustName where TransDate is less then 30 days ago.
I think I want something like:
date30 = (sum(Debit) - sum(Credit)) where CustName = XXXX AND (TransDate - date(now)) <= 30

I am very new to queries and don't know much, can you help me?

Jenay

 

[RichO]

Because of the summing & grouping you want to do, a query can't do this all by itself. You should display these results in a report after building your query.

In your query, set up 3 fields.

[CustName]
Expr1: DateDiff("d", [TransDate], Date())
Expr2: [Debit] - [Credit]

Set the criteria for Expr1 to <=30 and save this query.

Now build a report based on this query using the report wizard. Select CustName and Expr2 as the fields and then select CustName as a grouping level. Once the report is generated, go into design view and "drag" the Expr2 field from the detail section up into the CustName Header. Then change the control source to =Sum([Expr2]) and change its format property to Currency. Finally drag the lower border of the detail section upward to minimize it.

This should give you what you need.

 

[DALeffler]

You might try:

SELECT DISTINCTROW [MyTable].CustName, Sum([Debit])-Sum([Credit]) AS Date30
FROM [MyTable]
WHERE (((DateValue([TransDate]))>=DateValue(Now)-30 AND [MyTable].[CustName] Like [Type in First Few Chars of Customer Name:] & "*"))
GROUP BY [MyTable].CustName;

I think that syntax is ok...

(edit)- Well, now I think it's ok

Lemme know if that works...