Remove Records

[rvd48]

help needed to carry out a query, only want records under 12 months old to appear

i am building a database in Access for my final year: "A2 ICT" course which i have based on a computer shop, im trying to run a query which removes the records over 1 year old and just leaves the records that are under a year old, i tried this: =(Now())-"365" but when i run it all the records disappear so it carnt be right.

could someone help me out on this? im just looking for the code which will allow me to do the above.
any help would be much appreciated

 

[Mile-O]

DELETE *
FROM MyTable
WHERE (MyField < DateAdd("yyyy",-1,Date());

 

[rvd48]

Mile-O-Phile

DELETE *
FROM MyTable
WHERE (MyField < DateAdd("yyyy",-1,Date());

sorry about this, im a bit confused.

the field is called: date delivered/collected do i put that in instead of "MyField*?
for "yyyy" do i put this years date?
and for "date" do i put anything in?

thanks for the quick reply Mile-O-Phile.

 

[namliam]

Just to explain

When calculating dont use "" this makes it a string running you into trouble....

Doing calculations on dates use Date, not now. Now includes the time and may give inexpected (tho logical) results.

Your = is not what you want, you want to delete everything older than a year right? doing = will delete every exactly 1 year old (or in your case 365 days => read next)

doing your -365 your not taking into account leapyears (like 2004) doing dateadd like Mile did is much saver

This is why mile came up with his answer....

Regards

Edit to accomidate for the crosspost: Date() is a function like Now except (read above)
Yes put date delivered/collected instead of yourfield
exept you shouldnt use special characters like / and spaces in any field and or table names.....

 

[Mile-O]

the field is called: date delivered/collected do i put that in instead of "MyField?

Yes -in square brackets: [Date Delivered/Collected]

for "yyyy" do i put this years date?

No - it stays as "yyyy"

and for "date" do i put anything in?

I never said "date" - I said Date() meaning the Date function. That stays as is too.

 

[rvd48]

right so i've got it like this:
[Date DeliveredCollected]< DateAdd("yyyy",-1,Date());
this code is going into the query i've named "DeliveryTbl Query" under the header "date delivered collected"

run down on what im doing:
once a computer system is over 12 months i want it to go into an archive table, so i use an append query to do that, and a delete query to remove the record(s) from the main table "deliveryTbl"

i'll attach my work on to show you what i mean,
sorry it wouldnt let me, heres a picture anyway

 

[Mile-O]

i'll attach my work on to show you waht i mean,

By your description alone I already know what you mean.

 

[rvd48]

here's a picture: http://www.hursttilingsolutions.co.uk/M%20Acce1.jpg

 

[namliam]

[Date DeliveredCollected]< DateAdd("yyyy",-1,Date());

the [Date DeliveredCollected] part repricents the field (first row) < DateAdd("yyyy",-1,Date()) repricents the where clause (fifth row, where you now have "[Date DeliveredCollected]< DateAdd("yyyy",-1,Date())"

The full text was meant for insert in SQL.

Regards

 

[rvd48]

ok, i've inserted the code into SQL view: http://www.hursttilingsolutions.co.uk/access1.jpg

but when i click on "datasheet view" all the fields are still present and at the end Column it says "Expr1031" and gives back value's of -1 and 0 for some reason, http://www.hursttilingsolutions.co.uk/Datash2.jpg can any1 shed some light on this? and help me

 

[Mile-O]

I posted the criteria under the WHERE clause and NOT the SELECT clause.

 

[Mile-O]

And what's an A2 ICT course?

 

[Mile-O]

I'll also tell you that based on the screenshots I can surmise that your table structure is incorrect .

 

[rvd48]

A2 level= A'level
ICT= information, communication, technology.

i've made it into a WHERE statement, and it comes up with an error box: http://www.hursttilingsolutions.co.uk/SQL%20nu1.jpg

im a newbie, dont get too pissed off Mile-O-Phile

 

[Mile-O]

i've made it into a WHERE statement, and it comes up with an error box: http://www.hursttilingsolutions.co.uk/SQL%20nu1.jpg

You have the FROM and WHERE clause the wrong way around. The structure - as highlighted above - is

SELECT
FROM
WHERE

im a newbie, dont get too pissed off Mile-O-Phile

I'm not getting annoyed, I understand that you are a newbie but I'd expect a newbie still to have a bit of common sense:

I've given you the syntax previously and all you had to do was change it to match your object names. i.e this:

DELETE *
FROM DeliveryTbl
WHERE [Date Delivered Collected] < DateAdd("yyyy", -1, Date());

That's ALL you need.

As to my comment about your table structure I was just making the observation. I would expect that if you were doing a computer qualification and had any interest in it at all then you'd ask why your structure was wrong; maybe, even, take steps to amend it.

 

[rvd48]

done what you said mile, now get another error box: http://www.hursttilingsolutions.co.uk/SQL%20NO1.jpg

if i delete the asterix next to DELETE, then when i run it, a parameter box appears asking for a value.

 

[Mile-O]

You can delete EVERYTHING else from:

SELECT DeliveryTbl..... to [Win tv Cards],

The ONLY thing to be in the SQL window is the SQL statement I've given.

 

[rvd48]

Mile-O-Phile

You can delete EVERYTHING else from:
SELECT DeliveryTbl..... to [Win tv Cards],
The ONLY thing to be in the SQL window is the SQL statement I've given.

thanks for that mile

.took an age. basically, i had to just rename the "MY Field" to what i actually called it, dlete everything i've got in my SQL box and paste the code, simple ehh

master Mile: you say that my relationships are incorrect, i have made a copy of my relationships here:
1) http://www.hursttilingsolutions.co.uk/relati1.jpg
2) http://www.hursttilingsolutions.co.uk/relati2.jpg

can you look at it and tell me what you think is wrong? also the majority of the component tables are joined to the "deliveryTbl" by "CODE" but a few are joined by the actual component name e.g. "MONITORS" will this pose a problem mile?

 

[namliam]

Holy macaroni what a number of tables... geezzz...

1 thing i notice right away is a bunch of tables with a layout like:
Code
MFR
SomeField

These could all just be 1 table really....

Regards

 

[Mile-O]

Yep, rvd has created numerous one-to-one tables which are completely unnecessary.

To rvd, do a search on normalisation if you are interested.