A Way to automatically delete records

[Guest]

Hello all.
I am loking for a way to automatically delete records from a database (like
a reservations program) once the records DO NOT show a certain criteria (like
the reservation has not been confirmed or paid for). Is there any way to do
that?
Any help would be greatly appreciated.
Thanks in advance,
Dominic

 

[RuralGuy]

I would suggest developing a housekeeping routine
in a standard module and calling it periodically
(say every 24 hours) from a timer event on an
invisible form that is open all of the time.

Hello all.
I am loking for a way to automatically delete records from a database (like
a reservations program) once the records DO NOT show a certain criteria (like
the reservation has not been confirmed or paid for). Is there any way to do
that?
Any help would be greatly appreciated.
Thanks in advance,
Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

Thanks for the reply.
The only thing is....i have no idea what you are talking about, far less
have an idea where to start.

I am not THAT advanced, or at least have never come across working with
modules.

Any help on this would be GREATLY apreciated.

Thanks in advance.

Dominic

 

[RuralGuy]

Have you developed *any* of your application yet? If so, do you launch a form
on startup like a switchboard?

Thanks for the reply.
The only thing is....i have no idea what you are talking about, far less
have an idea where to start.

I am not THAT advanced, or at least have never come across working with
modules.

Any help on this would be GREATLY apreciated.

Thanks in advance.

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

Hi there.
I have pretty much done everything BUT the deletion of records. I have done
the forms, queries, reports, criteria, etc.
I DO have the switchboard opening on startup. I can't remember exactly what
i did (as i am using a modified version of a database I did about 4 years
ago, but that should not be a problem to check out).
Hope this helps explain a bit more.
regards,
Dominic

 

[Guest]

The criteria for deletion:
bookings that have NOT been confirmed within before 3 days of required date.
eg: on the 6th, all records that have not been confirmed for use on the 9th
will get deleted, or even moved to another table....whichever is
easier/better for the overall performance of the application
Dominic

 

[RuralGuy]

The next step is to develop the SELECT query that can pick out the records to
delete. Turning it into a delete query is a snap. Moving the records to a
history table is a little more complicated.

You might want to develop a very simple form that loads 1st and is invisible.
This form will remain open as long as the Application is running. It can be
very useful for a variety of reasons. This form can open your switchboad form.

The criteria for deletion:
bookings that have NOT been confirmed within before 3 days of required date.
eg: on the 6th, all records that have not been confirmed for use on the 9th
will get deleted, or even moved to another table....whichever is
easier/better for the overall performance of the application
Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RuralGuy,

Thanks for the help so far. It has taken me a while to get back because for
some reason I didn't receive a notification!

Anyway.

The DELETION option seems the one for me (because it's easier, and a lot
more FINAL!!)

How do I go about setting up the select query? I have tried, but I can't
seem to get it right! I am getting ALL of the records for that day.

Also, I have just realized today, that I am able to input TOO MANY
reservations for a specific event. I want to limit to 19. Is that possible?
So that ONLY when the "non confirmed" people are deleted (within the 3 days
of the event) can we actually add more. I have no idea if this is possible.

Thanks VERY much for your guidance so far...Sorry to be such a pain.

Regards,

Dominic

 

[RuralGuy]

Hi Dominic, see answers in line:

RuralGuy,

Thanks for the help so far. It has taken me a while to get back because for
some reason I didn't receive a notification!

I don't think this news group sends notifications.

Anyway.

The DELETION option seems the one for me (because it's easier, and a lot
more FINAL!!)

How do I go about setting up the select query? I have tried, but I can't
seem to get it right! I am getting ALL of the records for that day.

How about posting the SQL for the SELECT query you have that returns ALL
of the rcords for that day. We can go from there.

Also, I have just realized today, that I am able to input TOO MANY
reservations for a specific event. I want to limit to 19. Is that possible?
So that ONLY when the "non confirmed" people are deleted (within the 3 days
of the event) can we actually add more. I have no idea if this is possible.

Probably, but one thing at a time. Let's do the SELECT query 1st.

Thanks VERY much for your guidance so far...Sorry to be such a pain.

Regards,

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RG,
See questions in line. Only after i started to scroll sown did I understand!
haha

Hi Dominic, see answers in line:



I don't think this news group sends notifications.

They do, thankfully

How about posting the SQL for the SELECT query you have that returns ALL
of the rcords for that day. We can go from there.

"posting the SQL"...dunno about that part, BUT...... OK. I have a query that
will get all of the records for that day. I also have a query that will bring
up all of the UNCONFIRMED records for that day, and also one that brings up
ONLY the confirmed records for that day. I could get that far.
The only thing is, I have to manually type in the day (as per the criteria I
set to narrow down). It does not automatically get the "computer date" and
bring up the records.

 

[RuralGuy]

Hi Dominic,

I need to get a feel for your table. When you are in the design mode of the
query builder you can View>SQL View.

If you copy and paste that into a post it will tell me a lot about your table.

If you paste all three queries I'll learn even more!

RG,
See questions in line. Only after i started to scroll sown did I understand!
haha



They do, thankfully


"posting the SQL"...dunno about that part, BUT...... OK. I have a query that
will get all of the records for that day. I also have a query that will bring
up all of the UNCONFIRMED records for that day, and also one that brings up
ONLY the confirmed records for that day. I could get that far.
The only thing is, I have to manually type in the day (as per the criteria I
set to narrow down). It does not automatically get the "computer date" and
bring up the records.

<snip>
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

Here goes:
Pasengers confirmed:
SELECT [Barbuda Passenger List].[Date Booking Made], [Barbuda Passenger
List].Date, [Barbuda Passenger List].Flight, [Barbuda Passenger List].Name,
[Barbuda Passenger List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda
Passenger List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight DD/MM/YY]) AND
(([Barbuda Passenger List].Confirmed)=Yes));


Passengers NOT confirmed:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].[Date Booking
Made], [Barbuda Passenger List].Date, [Barbuda Passenger List].Flight,
[Barbuda Passenger List].Name, [Barbuda Passenger List].Pax, [Barbuda
Passenger List].Confirmed, [Barbuda Passenger List].[Contact Information],
[Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]) AND
(([Barbuda Passenger List].Confirmed)=No));


TOTAL passenger query:
SELECT [Barbuda Passenger List].[Date Booking Made], [Barbuda Passenger
List].Date, [Barbuda Passenger List].Flight, [Barbuda Passenger List].Name,
[Barbuda Passenger List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda
Passenger List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]));


I also had a criteria where you input the flight #, but I decided to get rid
of it, as ALL people not confirmed 3 days before must be deleted regardless
of flight.

Hope this helps.

Dominic

 

[RuralGuy]

Hi Dominic,

I believe this SELECT will pull those records that need deleting. You would
need to confirm that. Is the ID field unique in the table? Are you going to
make a report of these deletions? How many flights/day?

'Passengers Not Confirmed and needing deleting:
MySql = "SELECT " & _
"[Barbuda Passenger List].ID, " & _
"[Barbuda Passenger List].[Date BookingMade], " & _
"[Barbuda Passenger List].Date, " & _
"[Barbuda Passenger List].Flight, " & _
"[Barbuda Passenger List].Name, " & _
"[Barbuda Passenger List].Pax, " & _
"[Barbuda Passenger List].Confirmed, " & _
"[Barbuda Passenger List].[Contact Information], " & _
"[Barbuda Passenger List].Notes " & _
"FROM [Barbuda Passenger List] " & _
"WHERE ((([Barbuda Passenger List].Date) > DateAdd(""d"", -3, Date())) " & _
"AND (([Barbuda Passenger List].Confirmed)=No));"

<snip>

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RG. See questions/answers in line

Hi Dominic,

I believe this SELECT will pull those records that need deleting. You would
need to confirm that. Is the ID field unique in the table? Are you going to
make a report of these deletions? How many flights/day?

There are 4 flights a day

'Passengers Not Confirmed and needing deleting:
MySql = "SELECT " & _
"[Barbuda Passenger List].ID, " & _
"[Barbuda Passenger List].[Date BookingMade], " & _

There is an expression for date booking made...in the[] brackets? That is
the criteria, right? The criteria i want is the actaul DATE. The date booking
made info is not relevant. I will take it out of the query. Sorry, my
mistake. Here is the SQL for the new Passengers NOT confirmed:

SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]) AND
(([Barbuda Passenger List].Confirmed)=No));

Now. what I have been doing/looking at so far, is actually entering the date
of the flight itself. Would it be easier to just enter today's date 04/11/05,
and just limit the entries to the cutoff point, 07/11/05? Then these will be
the ones that will be deleted. I just realised that i was going about it the
long way around.

Is this easier to do than the way we are currently trying? I just think that
when the form opens on its own and picks up the current date from the system,
then it would be easier for the app to just select the records and get rid of
them.

What do you think?

"[Barbuda Passenger List].Date, " & _
"[Barbuda Passenger List].Flight, " & _
"[Barbuda Passenger List].Name, " & _
"[Barbuda Passenger List].Pax, " & _
"[Barbuda Passenger List].Confirmed, " & _
"[Barbuda Passenger List].[Contact Information], " & _
"[Barbuda Passenger List].Notes " & _
"FROM [Barbuda Passenger List] " & _
"WHERE ((([Barbuda Passenger List].Date) > DateAdd(""d"", -3, Date())) " & _
"AND (([Barbuda Passenger List].Confirmed)=No));"

<snip>

As usual, you are being a GREAT help.

Dominic

 

[RuralGuy]

Hi Dominic, Q&A in line below:

RG. See questions/answers in line

I believe this SELECT will pull those records that need deleting. You would
need to confirm that. Is the ID field unique in the table? Are you going to
make a report of these deletions?

How many flights/day? - answered, thanks

There are 4 flights a day

'Passengers Not Confirmed and needing deleting:
MySql = "SELECT " & _
"[Barbuda Passenger List].ID, " & _
"[Barbuda Passenger List].[Date BookingMade], " & _

There is an expression for date booking made...in the[] brackets? That is
the criteria, right? The criteria i want is the actaul DATE. The date booking
made info is not relevant. I will take it out of the query. Sorry, my
mistake. Here is the SQL for the new Passengers NOT confirmed:

SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]) AND
(([Barbuda Passenger List].Confirmed)=No));

Now. what I have been doing/looking at so far, is actually entering the date
of the flight itself. Would it be easier to just enter today's date 04/11/05,
and just limit the entries to the cutoff point, 07/11/05? Then these will be
the ones that will be deleted. I just realised that i was going about it the
long way around.

Is this easier to do than the way we are currently trying? I just think that
when the form opens on its own and picks up the current date from the system,
then it would be easier for the app to just select the records and get rid of
them.

What do you think?

"[Barbuda Passenger List].Date, " & _
"[Barbuda Passenger List].Flight, " & _
"[Barbuda Passenger List].Name, " & _
"[Barbuda Passenger List].Pax, " & _
"[Barbuda Passenger List].Confirmed, " & _
"[Barbuda Passenger List].[Contact Information], " & _
"[Barbuda Passenger List].Notes " & _
"FROM [Barbuda Passenger List] " & _
"WHERE ((([Barbuda Passenger List].Date) > DateAdd(""d"", -3, Date())) " & _

I think I got the Where clause incorrect:

"WHERE ((([Barbuda Passenger List].Date) <= DateAdd(""d"", 3, Date())) " & _


[Barbuda Passenger List].Date *is* the date of the flight, isn't it?

"AND (([Barbuda Passenger List].Confirmed)=No));"

<snip>

As usual, you are being a GREAT help.

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RG, Answers below.
Pity we can't have different colors to track the replies....these > are
getting confusing! haha

Hi Dominic, Q&A in line below:


I believe this SELECT will pull those records that need deleting. You would
need to confirm that. Is the ID field unique in the table? Are you going to
make a report of these deletions?

Yes, the ID field IS unique. It is an auto number. It goes up by 1 with
every entry. I woul dlike to make a report of the deletions if that is
possible, but I wanted to go one step at a time, lol, because as you can see,
i am in WAY over my head. the simple stuff is OK, but WOW, this is getting
VERY in-depth!

The select function pulls up every record in the table that is NOT
confirmed, not only the ones for the next 3 days. (I have modified my
existing database, hence, other records are in there dating back from 2000)

How many flights/day? - answered, thanks

There are 4 flights a day

'Passengers Not Confirmed and needing deleting:
MySql = "SELECT " & _
"[Barbuda Passenger List].ID, " & _
"[Barbuda Passenger List].[Date BookingMade], " & _

There is an expression for date booking made...in the[] brackets? That is
the criteria, right? The criteria i want is the actaul DATE. The date booking
made info is not relevant. I will take it out of the query. Sorry, my
mistake. Here is the SQL for the new Passengers NOT confirmed:

SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]) AND
(([Barbuda Passenger List].Confirmed)=No));

Now. what I have been doing/looking at so far, is actually entering the date
of the flight itself. Would it be easier to just enter today's date 04/11/05,
and just limit the entries to the cutoff point, 07/11/05? Then these will be
the ones that will be deleted. I just realised that i was going about it the
long way around.

Is this easier to do than the way we are currently trying? I just think that
when the form opens on its own and picks up the current date from the system,
then it would be easier for the app to just select the records and get rid of
them.

What do you think?

"[Barbuda Passenger List].Date, " & _
"[Barbuda Passenger List].Flight, " & _
"[Barbuda Passenger List].Name, " & _
"[Barbuda Passenger List].Pax, " & _
"[Barbuda Passenger List].Confirmed, " & _
"[Barbuda Passenger List].[Contact Information], " & _
"[Barbuda Passenger List].Notes " & _
"FROM [Barbuda Passenger List] " & _
"WHERE ((([Barbuda Passenger List].Date) > DateAdd(""d"", -3, Date())) " & _

I think I got the Where clause incorrect:

"WHERE ((([Barbuda Passenger List].Date) <= DateAdd(""d"", 3, Date())) " & _

This brings back ALL of the records. when I use:
WHERE ((([Barbuda Passenger List].Date)<=DateAdd("d",3,Date())) AND
(([Barbuda Passenger List].Confirmed)=No));
as the final line, it brings back all of the non-confirmed records. The
thing now, is to limit it to the next 3 days. I don't see anything there in
that clause that uses the current date as the base for the query (if you
understand what i mean). you are the pro at this, NOT me, so if i am missing
something, please excuse me.

[Barbuda Passenger List].Date *is* the date of the flight, isn't it?

Yes, it is the date of the flight.

"AND (([Barbuda Passenger List].Confirmed)=No));"

<snip>

As usual, you are being a GREAT help.

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

Q & A in line:

RG, Answers below.
Pity we can't have different colors to track the replies....these > are
getting confusing! haha

Actually, my NewsReader (Agent) shows in color. New message is in black and
previous messages are in blue.

Yes, the ID field IS unique. It is an auto number. It goes up by 1 with
every entry. I woul dlike to make a report of the deletions if that is
possible, but I wanted to go one step at a time, lol, because as you can see,
i am in WAY over my head. the simple stuff is OK, but WOW, this is getting
VERY in-depth!

The select function pulls up every record in the table that is NOT
confirmed, not only the ones for the next 3 days. (I have modified my
existing database, hence, other records are in there dating back from 2000)

This sounds normal since I don't think you have purged the UnConfirmed records
yet. Think what it would be pulling if every day you purged the unconfirmed
records.

How many flights/day? - answered, thanks

There are 4 flights a day


'Passengers Not Confirmed and needing deleting:
MySql = "SELECT " & _
"[Barbuda Passenger List].ID, " & _
"[Barbuda Passenger List].[Date BookingMade], " & _

There is an expression for date booking made...in the[] brackets? That is
the criteria, right? The criteria i want is the actaul DATE. The date booking
made info is not relevant. I will take it out of the query. Sorry, my
mistake. Here is the SQL for the new Passengers NOT confirmed:

SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)=[Enter Date of Flight]) AND
(([Barbuda Passenger List].Confirmed)=No));

Now. what I have been doing/looking at so far, is actually entering the date
of the flight itself. Would it be easier to just enter today's date 04/11/05,
and just limit the entries to the cutoff point, 07/11/05? Then these will be
the ones that will be deleted. I just realised that i was going about it the
long way around.

Is this easier to do than the way we are currently trying? I just think that
when the form opens on its own and picks up the current date from the system,
then it would be easier for the app to just select the records and get rid of
them.

What do you think?


"[Barbuda Passenger List].Date, " & _
"[Barbuda Passenger List].Flight, " & _
"[Barbuda Passenger List].Name, " & _
"[Barbuda Passenger List].Pax, " & _
"[Barbuda Passenger List].Confirmed, " & _
"[Barbuda Passenger List].[Contact Information], " & _
"[Barbuda Passenger List].Notes " & _
"FROM [Barbuda Passenger List] " & _
"WHERE ((([Barbuda Passenger List].Date) > DateAdd(""d"", -3, Date())) " & _

I think I got the Where clause incorrect:

"WHERE ((([Barbuda Passenger List].Date) <= DateAdd(""d"", 3, Date())) " & _

This brings back ALL of the records. when I use: -- In the next line:
WHERE ((([Barbuda Passenger List].Date)<=DateAdd("d",3,Date())) AND

DateAdd("d",3, >> Date()<< )) - That Date() is Today!

(([Barbuda Passenger List].Confirmed)=No));
as the final line, it brings back all of the non-confirmed records. The
thing now, is to limit it to the next 3 days. I don't see anything there in
that clause that uses the current date as the base for the query (if you
understand what i mean). you are the pro at this, NOT me, so if i am missing
something, please excuse me.

I believe we have the select statement we need. We do not need all of those
fields but we're still testing. You should make the report you need that will
show you whatever information you need to confirm the deletion. It will be big
right now and for testing we can limit the selection in query.

Just change the selection from "<=" to " ="
WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date())) AND

If you have an existing report that shows you what reservations you have then
you can copy that one and modify that report title and just hand it this
WhereCondition as an argument in the OpenReport command.

[Barbuda Passenger List].Date *is* the date of the flight, isn't it?

Yes, it is the date of the flight.

"AND (([Barbuda Passenger List].Confirmed)=No));"

<snip>

As usual, you are being a GREAT help.

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RG, See lines.
What is a good FREE newsreader?

This sounds normal since I don't think you have purged the UnConfirmed records
yet. Think what it would be pulling if every day you purged the unconfirmed
records.

I put in the line:
WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date()))
and the query now reads:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date()))) AND
((([Barbuda Passenger List].Confirmed)=No))

Now it does not bring up and records at all. Not sure why. I put in a couple
of records for 2 days time that have not been confirmed, but they are not
showing up.

The computer date is correct, so I don't know where it is going wrong.

This was the last working version:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)<=DateAdd("d",3,Date())) AND
(([Barbuda Passenger List].Confirmed)=No));

With this one, i don't have to input the date. I entered a record for the
11th, more than 3 days, and it was NOT brought up. This seems to be a good
version.

Where do we proceed from here?
Would it be to create a report? I would like to have a report done to show
all of the people that are going to be deleted, just for the record, but it
would be discarded at the end of the day anyway. And once these records have
been collected in the query, how do i actually delete them automatically?

Thanks,

Dominic

 

[RuralGuy]

Answered in line:

RG, See lines.
What is a good FREE newsreader?

There are many:
Microsoft Outlook Express - free with windows
Xnews: http://www.download.com/Xnews/3000-2164_4-10144167.html
Gravity: http://cws.internet.com/file/11436.htm
FreeAgent: http://www.forteinc.com/main/homepage.php

Google for others

This sounds normal since I don't think you have purged the UnConfirmed records
yet. Think what it would be pulling if every day you purged the unconfirmed
records.

I put in the line:
WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date()))
and the query now reads:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date()))) AND
((([Barbuda Passenger List].Confirmed)=No))

Now it does not bring up and records at all. Not sure why. I put in a couple
of records for 2 days time that have not been confirmed, but they are not
showing up.

The computer date is correct, so I don't know where it is going wrong.

The = said *only* those that are 3 days from scheduled departure.
Not 1 or 2 but exactly 3.

This was the last working version:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)<=DateAdd("d",3,Date())) AND
(([Barbuda Passenger List].Confirmed)=No));

With this one, i don't have to input the date. I entered a record for the
11th, more than 3 days, and it was NOT brought up. This seems to be a good
version.

This version <= says *all* unconfirmed reservations at least 3 days from
departure.

Where do we proceed from here?

I believe you should do a hard copy of all the deletions and keep it until after
the flight. You don't need an angry flyer at your counter without a reservation
they thought they had and no documentation to back up what happened.

Would it be to create a report? I would like to have a report done to show
all of the people that are going to be deleted, just for the record, but it
would be discarded at the end of the day anyway. And once these records have
been collected in the query, how do i actually delete them automatically?

Be sure the report has *all* of the relevant data as to why this reservation was
canceled and deleted.

After the report, we'll tackle the deletion. It is simply a matter of turning
your SELECT query into a DELETE query now that we know it selects correctly and
we have the documentation.

After that we quit taking reservations if the flight is full (19 Confirmed
reservations?) or just 19 reservations?

Thanks,

Dominic

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Guest]

RG: See in line

Answered in line:

RG, See lines.
What is a good FREE newsreader?

There are many:
Microsoft Outlook Express - free with windows
Xnews: http://www.download.com/Xnews/3000-2164_4-10144167.html
Gravity: http://cws.internet.com/file/11436.htm
FreeAgent: http://www.forteinc.com/main/homepage.php

Google for others

WHERE ((([Barbuda Passenger List].Date) = DateAdd("d",3,Date()))) AND
((([Barbuda Passenger List].Confirmed)=No))

The = said *only* those that are 3 days from scheduled departure.
Not 1 or 2 but exactly 3.

Understood. I have changed it to now read LESS THAN 4 as follows, and it now
works...does not show 4 days away, but does show 1 to 3:
SELECT [Barbuda Passenger List].ID, [Barbuda Passenger List].Date, [Barbuda
Passenger List].Flight, [Barbuda Passenger List].Name, [Barbuda Passenger
List].Pax, [Barbuda Passenger List].Confirmed, [Barbuda Passenger
List].[Contact Information], [Barbuda Passenger List].Notes
FROM [Barbuda Passenger List]
WHERE ((([Barbuda Passenger List].Date)<=DateAdd("d",4,Date())) AND
(([Barbuda Passenger List].Confirmed)=No))

I believe you should do a hard copy of all the deletions and keep it until after
the flight. You don't need an angry flyer at your counter without a reservation
they thought they had and no documentation to back up what happened.

I agree. Would it be possible for the form to run automatically, print, then
delete? That way we have a record of the records that have been
deleted.....run the report, then run another query (the same but with a
delete instruction).

Be sure the report has *all* of the relevant data as to why this reservation was
canceled and deleted.

The only reason for deletion of a record is for NON confirmation within the
required time period. If we need to change a record, I have another query
with date, flight and name criteria where all info can be edited or deleted
for any other reason.

After the report, we'll tackle the deletion. It is simply a matter of turning
your SELECT query into a DELETE query now that we know it selects correctly and
we have the documentation.

After that we quit taking reservations if the flight is full (19 Confirmed
reservations?) or just 19 reservations?

I think that the best solution is to limit ALL reservations to 19. That way,
the passengers are listed up until the cut off time. Then they are deleted
because of non confirmation. AFTER that, we could then take more CONFIRMED
ONLY reservations to make up the 19. We don't want to have ppl call up and
have their seats given away BEFORE the cut off time just because they paid
late.

Now, i guess i run a report based on the that query, correct?

Dominic