Syntax Error

[Duane]

Newbie question. Receiving a syntax error in INSERT INTO statement. If I
create the query in the QBE it works fine with the WHERE clause hard coded
as follows;

INSERT INTO tbl_Available ( [Group] )
SELECT Rot.Group
FROM Rot
WHERE (((Rot.MOD173)=1));

Here is my code!

Dim strSQL As String
Dim intMod As Integer

intMod = CLng(Me.Text0) Mod 196

strSQL = "DELETE * FROM tbl_Available"
CurrentDb.Execute strSQL


strSQL = "INSERT INTO tbl_Available Group " _
& "SELECT Rot.Group FROM Rot " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "
CurrentDb.Execute strSQL, dbFailOnError

Thanks in advance

 

[Graham Mandeno]

Hi Duane

In your QBE query, the field name "Group" is in parentheses. In your VBA it
is not.

This is one (unusual) case where the parentheses Access adds to a query in
the QBE designer are NOT superfluous

Change your code to:
strSQL = "INSERT INTO tbl_Available (Group) " _
...

 

[skydiver_2]

Hi Duane

In your QBE query, the field name "Group" is in parentheses.  In your VBA it
is not.

This is one (unusual) case where the parentheses Access adds to a query in
the QBE designer are NOT superfluous

Change your code to:
    strSQL = "INSERT INTO tbl_Available (Group) " _
        ...
--
Good Luck  

Graham Mandeno [Access MVP]
Auckland, New Zealand

Newbie question.  Receiving a syntax error in INSERT INTO statement.  If I
create the query in the QBE it works fine with the WHERE clause hard coded
as follows;

INSERT INTO tbl_Available ( [Group] )
SELECT Rot.Group
FROM Rot
WHERE (((Rot.MOD173)=1));

Here is my code!

Dim strSQL As String
Dim intMod As Integer

intMod = CLng(Me.Text0) Mod 196

strSQL = "DELETE * FROM tbl_Available"
CurrentDb.Execute strSQL

strSQL = "INSERT INTO tbl_Available Group " _
          & "SELECT Rot.Group FROM Rot " _
          & "WHERE ROT.[MOD" & intMod & "] = 1 "
CurrentDb.Execute strSQL, dbFailOnError

Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for the response Graham.

I tried it the way you suggested, prior to writing to the newsgroup,
but it was still giving me the same error. I discovered that I needed
to enclose Group with brackets and parentheses ([Group]). Sometimes
it is so easy, but other times it is so hard. And the fix is usually
so simple. That doens't even make sense.....

Thanks

 

[John W. Vinson]

I tried it the way you suggested, prior to writing to the newsgroup,
but it was still giving me the same error. I discovered that I needed
to enclose Group with brackets and parentheses ([Group]). Sometimes
it is so easy, but other times it is so hard. And the fix is usually
so simple. That doens't even make sense.....

Group is a reserved word in SQL (keyword for the Group By operation); it would
make sense that using it as a fieldname would mess up the query interpreter!
It can't tell if GROUP is a fieldname or if you're adding a GROUP clause to
your query.

Square brackets will force Access to interpret it as a fieldname (or
parameter)... but you'ld probably do better to change the fieldname.

 

[skydiver_2]

I tried it the way you suggested, prior to writing to the newsgroup,
but it was still giving me the same error.  I discovered that I needed
to enclose Group with brackets and parentheses ([Group]).  Sometimes
it is so easy, but other times it is so hard.  And the fix is usually
so simple.  That doens't even make sense.....

Group is a reserved word in SQL (keyword for the Group By operation); it would
make sense that using it as a fieldname would mess up the query interpreter!
It can't tell if GROUP is a fieldname or if you're adding a GROUP clause to
your query.

Square brackets will force Access to interpret it as a fieldname (or
parameter)... but you'ld probably do better to change the fieldname.

Thanks John.

I never even gave a thought that it was a reserved word in SQL. I
should have known. It is pretty obvious, but just looked right passed
it. I changed the field name, removed the brackets, and it worked
just exactly the way you said it should. Just more to remember

 

[Douglas J. Steele]

In addition to everything else that's been said, the fact that you've
apparently got field names like MOD0, MOD1, MOD2,... MOD195 is likely
indicative of the fact that your table hasn't been properly normalized.

 

[skydiver_2]

In addition to everything else that's been said, the fact that you've
apparently got field names like MOD0, MOD1, MOD2,... MOD195 is likely
indicative of the fact that your table hasn't been properly normalized.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Newbie question.  Receiving a syntax error in INSERT INTO statement.  If I
create the query in the QBE it works fine with the WHERE clause hard coded
as follows;

INSERT INTO tbl_Available ( [Group] )
SELECT Rot.Group
FROM Rot
WHERE (((Rot.MOD173)=1));

Here is my code!

Dim strSQL As String
Dim intMod As Integer

intMod = CLng(Me.Text0) Mod 196

strSQL = "DELETE * FROM tbl_Available"
CurrentDb.Execute strSQL

strSQL = "INSERT INTO tbl_Available Group " _
          & "SELECT Rot.Group FROM Rot " _
          & "WHERE ROT.[MOD" & intMod & "] = 1 "
CurrentDb.Execute strSQL, dbFailOnError

Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for your response Doug. I truly welcome any ideas you have.
Maybe you can give me some direction on how to make this project more
efficient.

I have one table, which is used strictly as a look up table of 49
different work schedules. There are 7 different types of schedules
with 7 variations of each schedule.

As a small example, the records in the table would be as follows;

RDOGroup Type MOD0 MOD1 MOD2 etc.... MOD195
1 Rotating 1 1
2 Rotating 1 1
3 - 7 omitted
8 Fixed 1 1
9 Fixed 1 1

The 1 in the MOD* field signifies the group which is off. I could
have used -1 and 0, but this is how the table was imported from
Paradox. I use the intMod = CLng(Me.Text0) Mod 196 function to
determine which group is off for that day. I am concatenating the
literal text MOD" & intMod so I know which column to query from this
table. Example: 05/01/2008 returns 173 which in turn = (MOD173).

I then INSERT INTO the (14 different groups) RDOCall table. The
groups in the RDOCall table are used for various Forms and Reports.
It actually works pretty slick and easy, but like I said, I open to
any ideas.

Short of the user having to know which RDO Group is off on a given
day, I don't know how I could normalize this table and still have all
the variations of schedules.

The big key is for the user to be able to enter a date in a textbox on
the Form, then click the A-List OR B-List (certain criteria apply) for
Overtime Button and a Form to add the overtime hours opens OR a Report
opens to show which employees are off, in order to call them in for
Overtime. Only those who are off on that date are populated into the
Form/Report.

There is flexibility in the fact that you have to select a tab for
which shift you are working. Select the Morning shift tab, then click
the A-List Report button to show who is off. Of course there are
preferences in the Employee table allowing the employee to not be
called for any overtime. A1, B1, A2, B2, A3, B3. Yes of No choices.
The same applies to adding hours for those who have been called in or
those who refused overtime.

Note: A-List is for those who are off on THEIR shift and wish to work
overtime. The B-List is for those who are on OTHER shifts wishing to
work overtime on your shift. This is all voluntary overtime. There
is a completely different set of rules for the Mandate List.

Thanks in advance.

 

[Douglas J. Steele]

The general expression is "Rows are cheap, fields are expensive".

For the data you showed, it should be something like:

RDOGroup Type MOD
1 Rotating 0
1 Rotating 1
2 Rotating 1
2 Rotating 2
3 - 7 omitted
8 Fixed 1
8 Fixed 2
9 Fixed 0
9 Fixed 1

Jeff Conrad lists a number of good resources for database modelling at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In addition to everything else that's been said, the fact that you've
apparently got field names like MOD0, MOD1, MOD2,... MOD195 is likely
indicative of the fact that your table hasn't been properly normalized.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Newbie question. Receiving a syntax error in INSERT INTO statement. If I
create the query in the QBE it works fine with the WHERE clause hard
coded
as follows;

INSERT INTO tbl_Available ( [Group] )
SELECT Rot.Group
FROM Rot
WHERE (((Rot.MOD173)=1));

Here is my code!

Dim strSQL As String
Dim intMod As Integer

intMod = CLng(Me.Text0) Mod 196

strSQL = "DELETE * FROM tbl_Available"
CurrentDb.Execute strSQL

strSQL = "INSERT INTO tbl_Available Group " _
& "SELECT Rot.Group FROM Rot " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "
CurrentDb.Execute strSQL, dbFailOnError

Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for your response Doug. I truly welcome any ideas you have.
Maybe you can give me some direction on how to make this project more
efficient.

I have one table, which is used strictly as a look up table of 49
different work schedules. There are 7 different types of schedules
with 7 variations of each schedule.

As a small example, the records in the table would be as follows;

RDOGroup Type MOD0 MOD1 MOD2 etc.... MOD195
1 Rotating 1 1
2 Rotating 1 1
3 - 7 omitted
8 Fixed 1 1
9 Fixed 1 1

The 1 in the MOD* field signifies the group which is off. I could
have used -1 and 0, but this is how the table was imported from
Paradox. I use the intMod = CLng(Me.Text0) Mod 196 function to
determine which group is off for that day. I am concatenating the
literal text MOD" & intMod so I know which column to query from this
table. Example: 05/01/2008 returns 173 which in turn = (MOD173).

I then INSERT INTO the (14 different groups) RDOCall table. The
groups in the RDOCall table are used for various Forms and Reports.
It actually works pretty slick and easy, but like I said, I open to
any ideas.

Short of the user having to know which RDO Group is off on a given
day, I don't know how I could normalize this table and still have all
the variations of schedules.

The big key is for the user to be able to enter a date in a textbox on
the Form, then click the A-List OR B-List (certain criteria apply) for
Overtime Button and a Form to add the overtime hours opens OR a Report
opens to show which employees are off, in order to call them in for
Overtime. Only those who are off on that date are populated into the
Form/Report.

There is flexibility in the fact that you have to select a tab for
which shift you are working. Select the Morning shift tab, then click
the A-List Report button to show who is off. Of course there are
preferences in the Employee table allowing the employee to not be
called for any overtime. A1, B1, A2, B2, A3, B3. Yes of No choices.
The same applies to adding hours for those who have been called in or
those who refused overtime.

Note: A-List is for those who are off on THEIR shift and wish to work
overtime. The B-List is for those who are on OTHER shifts wishing to
work overtime on your shift. This is all voluntary overtime. There
is a completely different set of rules for the Mandate List.

Thanks in advance.

 

[skydiver_2]

The general expression is "Rows are cheap, fields are expensive".

For the data you showed, it should be something like:

RDOGroup     Type          MOD
      1           Rotating            0
      1           Rotating            1
      2           Rotating            1
      2           Rotating            2
      3 - 7 omitted
      8           Fixed                 1
      8           Fixed                 2
      9           Fixed                 0
      9           Fixed                 1

Jeff Conrad lists a number of good resources for database modelling athttp://www.accessmvp.com/JConrad/accessjunkie/resources.html#Database...

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

In addition to everything else that's been said, the fact that you've
apparently got field names like MOD0, MOD1, MOD2,... MOD195 is likely
indicative of the fact that your table hasn't been properly normalized.

news:O%[email protected]...

Newbie question. Receiving a syntax error in INSERT INTO statement. IfI
create the query in the QBE it works fine with the WHERE clause hard
coded
as follows;
INSERT INTO tbl_Available ( [Group] )
SELECT Rot.Group
FROM Rot
WHERE (((Rot.MOD173)=1));
Here is my code!
Dim strSQL As String
Dim intMod As Integer
intMod = CLng(Me.Text0) Mod 196
strSQL = "DELETE * FROM tbl_Available"
CurrentDb.Execute strSQL
strSQL = "INSERT INTO tbl_Available Group " _
& "SELECT Rot.Group FROM Rot " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "
CurrentDb.Execute strSQL, dbFailOnError
Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for your response Doug.  I truly welcome any ideas you have.
Maybe you can give me some direction on how to make this project more
efficient.

I have one table, which is used strictly as a look up table of 49
different work schedules.  There are 7 different types of schedules
with 7 variations of each schedule.

As a small example, the records in the table would be as follows;

RDOGroup     Type          MOD0    MOD1    MOD2  etc....  MOD195
      1           Rotating            1           1
      2           Rotating                        1            1
      3 - 7 omitted
      8           Fixed                             1            1
      9           Fixed                1           1

The 1 in the MOD* field signifies the group which is off.  I could
have used -1 and 0, but this is how the table was imported from
Paradox.  I use the intMod = CLng(Me.Text0) Mod 196 function to
determine which group is off for that day.  I am concatenating the
literal text MOD" & intMod so I know which column to query from this
table.  Example: 05/01/2008 returns 173 which in turn = (MOD173).

I then INSERT INTO the (14 different groups) RDOCall table.  The
groups in the RDOCall table are used for various Forms and Reports.
It actually works pretty slick and easy, but like I said, I open to
any ideas.

Short of the user having to know which RDO Group  is off on a given
day, I don't know how I could normalize this table and still have all
the variations of schedules.

The big key is for the user to be able to enter a date in a textbox on
the Form, then click the A-List OR B-List (certain criteria apply) for
Overtime Button and a Form to add the overtime hours opens OR a Report
opens to show which employees are off, in order to call them in for
Overtime.  Only those who are off on that date are populated into the
Form/Report.

There is flexibility in the fact that you have to select a tab for
which shift you are working.  Select the Morning shift tab, then click
the A-List Report button to show who is off.  Of course there are
preferences in the Employee table allowing the employee to not be
called for any overtime.  A1, B1, A2, B2, A3, B3.  Yes of No choices.
The same applies to adding hours for those who have been called in or
those who refused overtime.

Note:  A-List is for those who are off on THEIR shift and wish to work
overtime.  The B-List is for those who are on OTHER shifts wishing to
work overtime on your shift.  This is all voluntary overtime.  There
is a completely different set of rules for the Mandate List.

Thanks in advance.- Hide quoted text -

- Show quoted text -

Thank you again for responding Doug. I do understand the basics of
Normalization, and I emphasize the basics. If I understand what you
are telling me, it would be better to have only 3 fields in the table.

RDOGroup Type MOD

I do realize that a normalized table should not have this many like
fields. It is wrong by all standards.

Let’s just take 2 of the 49 rows for an example:
RDOGroup Type MOD0 MOD1 MOD2 MOD3 MOD4 MOD5 MOD6 MOD7 MOD8
MOD9 MOD10
1 Rotate 1 1 0 0
0 0 0 0 1 1 0
2 Rotate 0 1 1 0
0 0 0 0 0 1 1
(If this turns out okay it will be a miracle)

The 1 represents days off and 0 reprsents days worked. As it stands
right now, I run my MOD function to determine which column I need to
query. intMod = CLng(Me.Text0) Mod 196 returns me 173. Lets say the
date was May 26,2008. The MOD function would return 2. Concatenate
that with the string MOD and I would know to query MOD2.

SELECT Rot.RDOGroup, Rot.Type, Rot.MOD2
FROM Rot
WHERE Rot.MOD2=1;

The result would be

RDOGroup Type MOD2
2 7 Rotate 1
3 7 Rotate 1
9 Fixed 1
10 Fixed 1
15 10-4 1
16 10-4 1
17 10-4 1
28 10-4 1
30 7-2-3-2 1
31 7-2-3-2 1
33 7-2-3-2 1
42 7-2-3-2 1
44 28 Rotate 1
45 28 Rotate 1

The only thing I can figure is that you are saying it would be better
if I had the following fields:

RDOGroup Type MOD OffON
1 Rotate 0 1
1 Rotate 1 1
1 Rotate 2 0
1 Rotate 3 0
1 Rotate 4 0
1 Rotate 5 0
1 Rotate 6 0
1 Rotate 7 0
1 Rotate 8 1
1 Rotate 9 1

I would continue this until I complete MOD 195. Then I would start
group 2 and complete my MOD0 - MOD195, Then RDOGroup 3, 4, and so on
until RDOGroup 49, for a total of 9604 rows. This way I could
construct my query to

SELECT Rot.RDOGroup, Rot.Type
FROM Rot
WHERE Rot.MOD=2 AND Rot.OffOn =1;

Thanks for your input.