June 22, 2011 at 3:46 am
Good Morning All
looking for some help with a problem I have been tasked with
I am creating a new Visitor Book system, for people to sign in when they enter the building and sign out when they leave
when the visitor leaves their info needs to be deleted from the Main table (tblmain) and pasted into the Out table (tblout)
I have tried the below codes in SQL but they dont cdon'tor delete any data
INSERT INTO tblout
SELECT *
FROM tblmain
WHERE 'Depart Time' = FORMAT (Time(),"hh:mm");
DELETE *
FROM tblMain
WHERE 'Depart Time' = FORMAT (Time(),"hh:mm");
any help with this would be fantastic
Thanks
Matthew
June 22, 2011 at 4:07 am
Are you using SQL Server?
Failing to plan is Planning to fail
June 22, 2011 at 4:08 am
I am using Microsoft Access 2010, with the SQL view on Queries
June 22, 2011 at 4:12 am
moving the data more complex than it need to...
i don't see a need to move the data, other than it makes "sense" to someone (the data is different due to a status, lets move it to a different table so i understand it better)
I'd Create two views instead, both based on the tblMain.
if you know the [Depart Time] is the indicator of whether the are in or out, :
CREATE VW_StillHere
as
SELECT * FROM tblMain WHERE [Depart Time] IS NULL
GO
SELECT * FROM tblOutWHERE [Depart Time] IS NOT NULL
Lowell
June 22, 2011 at 4:18 am
the depart time is generated when the visitor clicks the Out button on the access form (taken from the computers time)
June 22, 2011 at 4:32 am
m.dodd (6/22/2011)
the depart time is generated when the visitor clicks the Out button on the access form (taken from the computers time)
the method of entry into a table wouldn't really matter, would it? the depart time either exists or it does not...
the moment you query the views, it would instantly be in one or the other, but not both views....and always be int he main table
Lowell
June 22, 2011 at 4:38 am
Hi Lowell
thanks for your advise and it does make sense however
CREATE TABLE tblIN
AS
SELECT * FROM tblmain WHERE [DEPART TIME] IS NULL
GO
SELECT * FROM tblout WHERE [DEPART TIME] IS NOT NULL
does not work -returns a syntax error
June 22, 2011 at 4:42 am
since you are using access, those are two different queries;
the GO statement is valid for SQL Server;
each of the two queries needs to be saved separately...been a while since i touched Access
Lowell
June 22, 2011 at 4:47 am
so if i create 2 seperate queries
CREATE TABLE tblIN
AS
SELECT * FROM tblmain WHERE [DEPART TIME] IS NULL
CREATE TABLE tblIN
AS
SELECT * FROM tblout WHERE [DEPART TIME] IS NOT NULL
should that be better??
June 22, 2011 at 8:37 am
Ok Got an Interesting one
i used this
INSERT INTO tblout
SELECT *
FROM tblMain
WHERE (tblMain.Depart_Time = Format (Time(), "hh:mm"));
which works to a semi-fashion,
what this seems to do is it selects the previous record in the list and moves that across the tables, not quite sure why
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply