[MSAccessProfessionals] Different data in same column

Friends,

 

We have a new timekeeping system and I’m used to importing hours from the old system into a database where it can be sliced, diced, grouped, and etc.  Unfortunately, now when I’m running a report to extract the hours, there are so many types of paycodes used that the report puts all the paycodes in same column and the hours associated with each paycode in another “shared” column instead of making a column for each paycode and putting the hours in the correct column.  See examples:

This is the report I get.

Employee ID	Paycode	Total Hours
30252579	PTO Scheduled Current Year	3.50
30252579	Regular	36.57
30244320	Overtime	0.20
30244320	Regular	40.00
30250342	Regular	40.00
30250342	Overtime	0.18
30079782	Overtime	0.38
30079782	Regular	40.00
30268802	Regular	40.00
30268802	Overtime	0.33

 

This is how I want the data with 1 line per employee ID:

Employee ID	Regular	OverTime	PTO
30252579	36.56667		3.5
30244320	40	0.2
30250342	40	0.183333333
30079782	40	0.383333333
30268802	40	0.333333333

 

Are there any easy ways to do this?  My thoughts are to query the top table with If statements to then use a totals query on the first query to put all the records into one row.    

 

 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

Thanks Mark. I'll have to test that.  I tried it earlier and it wouldn't compile but then again it was while I had unmatched parenthesis. 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Mark Burns via groups.io
Sent: Wednesday, October 15, 2025 9:29 AM
To: MSAccessProfessionals@groups.io; Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io>
Subject: Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

 

Also, you were mixing both ""s and ''s (double quotes and apostrophes) in the same query test - which is another potential source of confusion. SQL Server/T-SQL prefers single quotes (apostrophes). So it's better to be consistent in that regard. 

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

Also, you were mixing both ""s and ''s (double quotes and apostrophes) in the same query test - which is another potential source of confusion. SQL Server/T-SQL prefers single quotes (apostrophes). So it's better to be consistent in that regard.

On 10/15/2025 8:34 AM EDT Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:

 

 

Thanks Duane. You are correct the number of "(" and ")" aren't equal. Evening them out fixed the 3075 error.  Thanks for the other syntax advice also.  I'll try it.  And yes, UPDATED_58 actually a text field. It's from an SQL table for a program I didn't write. Not sure why they chose to add zeros to the front.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom via groups.io
Sent: Wednesday, October 15, 2025 7:30 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

 

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. Is UPDATED_58 actually a text field rather than a yes/no field? Also, since there is only the single table, you can remove all of the [Work General

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. 

 

Is UPDATED_58 actually a text field rather than a yes/no field?

 

Also, since there is only the single table, you can remove all of the [Work General Ledger]."s. The table name is only needed following the UPDATE. 

 

Duane

 

 

On Oct 15, 2025, at 7:19 AM, Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:

 

Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from "N" to "Y" for records with a batch number (text field) that comes from a combo box.  I'm getting a runtime error 3075, syntax error in query expression and can't figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks 

 

 

Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

Also, you were mixing both ""s and ''s (double quotes and apostrophes) in the same query test - which is another potential source of confusion. SQL Server/T-SQL prefers single quotes (apostrophes). So it's better to be consistent in that regard.

On 10/15/2025 8:34 AM EDT Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:

 

 

Thanks Duane. You are correct the number of "(" and ")" aren't equal. Evening them out fixed the 3075 error.  Thanks for the other syntax advice also.  I'll try it.  And yes, UPDATED_58 actually a text field. It's from an SQL table for a program I didn't write. Not sure why they chose to add zeros to the front.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom via groups.io
Sent: Wednesday, October 15, 2025 7:30 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

 

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. Is UPDATED_58 actually a text field rather than a yes/no field? Also, since there is only the single table, you can remove all of the [Work General

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. 

 

Is UPDATED_58 actually a text field rather than a yes/no field?

 

Also, since there is only the single table, you can remove all of the [Work General Ledger]."s. The table name is only needed following the UPDATE. 

 

Duane

 

On Oct 15, 2025, at 7:19 AM, Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:

 

Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from "N" to "Y" for records with a batch number (text field) that comes from a combo box.  I'm getting a runtime error 3075, syntax error in query expression and can't figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks 

 

 

Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

Thanks Duane. You are correct the number of "(" and ")" aren't equal. Evening them out fixed the 3075 error.  Thanks for the other syntax advice also.  I'll try it.  And yes, UPDATED_58 actually a text field. It's from an SQL table for a program I didn't write. Not sure why they chose to add zeros to the front.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom via groups.io
Sent: Wednesday, October 15, 2025 7:30 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

 

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. Is UPDATED_58 actually a text field rather than a yes/no field? Also, since there is only the single table, you can remove all of the [Work General

ZjQcmQRYFpfptBannerStart

This Message Is From an Untrusted Sender You have not previously corresponded with this sender.

ZjQcmQRYFpfptBannerEnd

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. 

 

Is UPDATED_58 actually a text field rather than a yes/no field?

 

Also, since there is only the single table, you can remove all of the [Work General Ledger]."s. The table name is only needed following the UPDATE. 

 

Duane

 

On Oct 15, 2025, at 7:19 AM, Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:



Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from "N" to "Y" for records with a batch number (text field) that comes from a combo box.  I'm getting a runtime error 3075, syntax error in query expression and can't figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks 

 

Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

I think the number of "(" and ")" aren't equal. All of them are unnecessary and should be removed. 

Is UPDATED_58 actually a text field rather than a yes/no field?

Also, since there is only the single table, you can remove all of the [Work General Ledger]."s. The table name is only needed following the UPDATE. 

Duane

On Oct 15, 2025, at 7:19 AM, Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:



Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from "N" to "Y" for records with a batch number (text field) that comes from a combo box.  I'm getting a runtime error 3075, syntax error in query expression and can't figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks 

 

Re: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

Never mind.  I just figured out the problem was with the parenthesis. Too many in some places and they weren’t balanced. 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Doyce Winberry via groups.io
Sent: Wednesday, October 15, 2025 7:19 AM
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

 

Hello friends, I need help with syntax on an SQL statement. It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from “N” to “Y” for records with a batch number (text field) that comes from

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd

Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from “N” to “Y” for records with a batch number (text field) that comes from a combo box.  I’m getting a runtime error 3075, syntax error in query expression and can’t figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks in advance. 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

[MSAccessProfessionals] Runtime Error '3075' Syntax error in query expression

Hello friends,

 

I need help with syntax on an SQL statement.  It is pulling criteria from a combo box. I want it up update a field, UPDATED_58 from “N” to “Y” for records with a batch number (text field) that comes from a combo box.  I’m getting a runtime error 3075, syntax error in query expression and can’t figure out what is wrong.  Here is the SQL from VBA:

strSQL = "UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = ""Y""" & _

        " WHERE ((([Work General Ledger].UPDATED_58)=""N"") AND (([Work General Ledger].BATCH_58)= '" & [Forms]![frmOracleGLUpload]![cboBatch] & "' AND (([Work General Ledger].FROM_58)=""GL""));"

  

000006532 is the text field with the batch number for this example.  Here is what I get with debug.print:

UPDATE [Work General Ledger] SET [Work General Ledger].UPDATED_58 = "Y" WHERE ((([Work General Ledger].UPDATED_58)="N") AND (([Work General Ledger].BATCH_58)= '000006532' AND (([Work General Ledger].FROM_58)="GL"));

 

Thanks in advance. 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269