Last 7 days transactions query

[matthoward]

Good Afternoon. I have a date field in my append query. What I want to do is bring down information for the last 7 days only. I havde tried Last7Days which is a command I stole out of crystal but no luck. Does anyone have any ideas?


Thanks Matt

 

[matthoward]

I must add that I want to run it daily for the last seven days not once a week for the last seven days,

Thanks

 

[scriverb]

The WHERE portion of the query should read as follows with the appropriate updates for your table and field names:

WHERE [tblYourTableName].[YourDateField] >= Date - 7;

Post back if you have any more questions. If you still need more assistance please post the SQL for your Append query. I can use that to update appropriately using your table and field names.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[matthoward]

Thank you for your help I am just a little lost still
this is my SQL commands
INSERT INTO lend_trans ( [Product Type], [Internal Account Nbr], [Txn sequence], [Transaction Date], [Transaction Time], [Transaction Display], [Value Date], [Posting Indicator], [Posting Reference], [Transaction Amount], [Txn Future Balance], [Txn Current Balance], [Txn Arrears Balance], [Txn Pnlty Int Balance], [Waive Fee], [Txn Source], [Txn Source Surrogate], [Organisation Unit Type id], [Organisation Unit id], [Key person initials], [Transaction code], [Teller Session ID], [Clearance Date], [Txn Use Code], [Transaction Slip Nbr], [Spare Txn Flag 1], [Spare Txn Flag 2], [Spare Txn Date] )
SELECT SOV40PDTA_DDEECPP.EUEECD, SOV40PDTA_DDEECPP.EUNBCD, SOV40PDTA_DDEECPP.EUE0N7, SOV40PDTA_DDEECPP.EUCGD7, SOV40PDTA_DDEECPP.EUAVTM, SOV40PDTA_DDEECPP.EUAJVN, SOV40PDTA_DDEECPP.EUCHD7, SOV40PDTA_DDEECPP.EUELS7, SOV40PDTA_DDEECPP.EUD5C7, SOV40PDTA_DDEECPP.EUBNV7, SOV40PDTA_DDEECPP.EUB1V7, SOV40PDTA_DDEECPP.EUYLV7, SOV40PDTA_DDEECPP.EUN6VA, SOV40PDTA_DDEECPP.EUOKVA, SOV40PDTA_DDEECPP.EUUDST, SOV40PDTA_DDEECPP.EUREST, SOV40PDTA_DDEECPP.EUAMCD, SOV40PDTA_DDEECPP.EUHRCD, SOV40PDTA_DDEECPP.EUHUCD, SOV40PDTA_DDEECPP.EUI0CD, SOV40PDTA_DDEECPP.EUEBCD, SOV40PDTA_DDEECPP.EUACNR, SOV40PDTA_DDEECPP.EUOGDT, SOV40PDTA_DDEECPP.EUEOSZ, SOV40PDTA_DDEECPP.EUB3S, SOV40PDTA_DDEECPP.EUB4S, SOV40PDTA_DDEECPP.EUJEST, SOV40PDTA_DDEECPP.EUOHDT
FROM SOV40PDTA_DDEECPP
WHERE (((SOV40PDTA_DDEECPP.EUCGD7) Not In (SELECT EUNBCD FROM lend_trans WHERE EUCGD7>=Date()-7)) AND ((SOV40PDTA_DDEECPP.EUEBCD)="W/OFF&quot);

I have a query called append_lend_trans which when run updates the lend_trans table. I just want to bring down Lending transactions for the last 7 days. If you need any more info give me a shout and thank you for your help so far,

 

[scriverb]

From your SQL below:
WHERE (((SOV40PDTA_DDEECPP.EUCGD7) Not In (SELECT EUNBCD FROM lend_trans WHERE EUCGD7>=Date()-7)) AND ((SOV40PDTA_DDEECPP.EUEBCD)="W/OFF&quot);


The subselect in red seems as if it would select the records that you asked for in your original posting. Is there a field called EUCGD7 in both tables? You see the subselect is performing the WHERE clause correctly but you are returning only the field EUNBCD and making a NOT IN comparison to SOV40PDTA_DDEECPP.EUCGD7. I take it that EUCGD7 is a date field. Is EUNBCD also? Please clarify these questions and concerns please.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[matthoward]

EUCGD7 is the date field it appends the field in lend_trans called Transaction Date. Am I writing it out wrong and where should I be putting the Formula, Should I go under EUCGD7 or in the SQL or in the new field in the append query?


Thanks again for your help,

Cheers Matt

 

[matthoward]

Also EUNBCD is the field that I tell it that I just want the Payment transactions coming down.

Cheers

 

[scriverb]

You see:
(((SOV40PDTA_DDEECPP.EUCGD7) Not In (SELECT EUNBCD FROM lend_trans WHERE EUCGD7>=Date()-7))

When you say EUCGD7 Not In (Select EUNBCD. . . both EUCGD7 AND EUNBCD both have to be date fields. You are comparing them in the NOT IN function clause to say select all records where the date field EUCGD7 is not included in the list of date fields EUNBCD.

From what you just posted I don't think they are both date fields. Please explain.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[matthoward]

This is the sql without the command you gave me
INSERT INTO lend_trans ( [Product Type], [Internal Account Nbr], [Txn sequence], [Transaction Date], [Transaction Time], [Transaction Display], [Value Date], [Posting Indicator], [Posting Reference], [Transaction Amount], [Txn Future Balance], [Txn Current Balance], [Txn Arrears Balance], [Txn Pnlty Int Balance], [Waive Fee], [Txn Source], [Txn Source Surrogate], [Organisation Unit Type id], [Organisation Unit id], [Key person initials], [Transaction code], [Teller Session ID], [Clearance Date], [Txn Use Code], [Transaction Slip Nbr], [Spare Txn Flag 1], [Spare Txn Flag 2], [Spare Txn Date] )
SELECT SOV40PDTA_DDEECPP.EUEECD, SOV40PDTA_DDEECPP.EUNBCD, SOV40PDTA_DDEECPP.EUE0N7, SOV40PDTA_DDEECPP.EUCGD7, SOV40PDTA_DDEECPP.EUAVTM, SOV40PDTA_DDEECPP.EUAJVN, SOV40PDTA_DDEECPP.EUCHD7, SOV40PDTA_DDEECPP.EUELS7, SOV40PDTA_DDEECPP.EUD5C7, SOV40PDTA_DDEECPP.EUBNV7, SOV40PDTA_DDEECPP.EUB1V7, SOV40PDTA_DDEECPP.EUYLV7, SOV40PDTA_DDEECPP.EUN6VA, SOV40PDTA_DDEECPP.EUOKVA, SOV40PDTA_DDEECPP.EUUDST, SOV40PDTA_DDEECPP.EUREST, SOV40PDTA_DDEECPP.EUAMCD, SOV40PDTA_DDEECPP.EUHRCD, SOV40PDTA_DDEECPP.EUHUCD, SOV40PDTA_DDEECPP.EUI0CD, SOV40PDTA_DDEECPP.EUEBCD, SOV40PDTA_DDEECPP.EUACNR, SOV40PDTA_DDEECPP.EUOGDT, SOV40PDTA_DDEECPP.EUEOSZ, SOV40PDTA_DDEECPP.EUB3S, SOV40PDTA_DDEECPP.EUB4S, SOV40PDTA_DDEECPP.EUJEST, SOV40PDTA_DDEECPP.EUOHDT
FROM SOV40PDTA_DDEECPP
WHERE (((SOV40PDTA_DDEECPP.EUEBCD)="PAYMT&quot);

In the field which brings down the date information it is as follows
Field EUCGD7
Table SOV40PDTA_DDEECPP
Append to Transaction Date

It is belonging to the query the query called append_lend_trans

The table that all the information is going to after is has appended the field of information is called
lend_trans

It contains the following criteria
Field Name Transaction Date
Field Type Number


I hope this helps,

Thanks Matt