Stack Overflow
Stack Overflow

Questions tagged [jet-sql]

Jet-SQL is an SQL dialect used in Microsoft Access.

61 questions
1
vote
1 answer

MS Access SQL If Row(n) = Row(n-1)

I am developing an access DB where in one of the table inputs, duplicates regularly appear in (what I would eventually like to be) the primary key due to the use of some old software for reasons as can be seen below: ID | Fluid | Designer …
OriginalDisplayName
  • 13
  • 5
1
vote
2 answers

Is this possible? MS Access query selecting a field to be on top and others will be in alpha order

Example i have a table: ---------------------- ID. | Color ---------------------- A1. | Red A2. | Yellow A3. | Blue A4. | Black ----------------------- Is this possible to sort a table like this? specify a field to be on top (example:…
Melz
  • 13
  • 5
1
vote
1 answer

Determine if a shipment has been billed for the month

I have a warehousing database that uses the "ShipRefs" (Labeled as shipment on the form) field to associate items to a particular shipment. I am now creating an invoicing form in order to generate a bill for that customer per shipment per month. I…
Rynoc
  • 45
  • 1
  • 1
  • 9
1
vote
1 answer

Access 2010 Error 3035 System Resources Exceeded on Update Query

I've got a routine that imports and performs some transformations on a 481 MB text file. The routine crashes on this code block for one workstation (but works fine on our other workstations): ' All of the eleven-digit telephone numbers encountered…
Project_H
  • 59
  • 2
  • 10
1
vote
0 answers

Import Data from Access using JET SQL (having a Custom Function)

I have an MS access 2010 database which is connected to a MS Excel 2010 for reporting purpose. I have linked the Excel to Access using Get External Data option in excel. With few changes in the reporting requirements I had to create a new query in…
Youbaraj Sharma
  • 1,295
  • 3
  • 17
  • 34
1
vote
2 answers

Microsoft SQL Server Express vs ACE

Currently, I'm developing a C#-based program for a small rental company (3 locations). Right now, they use MS Access 97 (Jet SQL based) as database and I wish to upgrade this. However, I do want to keep Access as Front-end, since I will be gone…
JeroenM
  • 117
  • 1
  • 9
1
vote
2 answers

MS Access queries with inline functions can't be exposed

I have an MS Access 2003 database that contains the following query: SELECT Replace(Trim(TABLE_A.Field_01), "XXX", "YYY") AS FLD01 FROM TABLE_A If I do an "Import Data" with Excel from this Access database, I can't find the name of this query that…
Gao Wei
  • 79
  • 1
  • 11
0
votes
0 answers

Deleting table data based on sub select condition using INNER JOIN

I have duplicate records that I am trying to remove in an Access DB using Jet SQL. I have managed to get a sub select query working that isolates the data points I am interested in using as a conditional call. However, I cannot get any variation of…
Dre Day
  • 338
  • 2
  • 8
0
votes
1 answer

Can I isolate duplicate values + a unique value field? - Jet SQL

I have a situation where I have columns 1,2,3,4 where 1,2,3 can be duplicated values across the row and 4 can be unique. I am tryin to write a query that will give me duplicates of 1,2,3 and the difference of 4. So it would like, 1 2 3 4 a b c 5 a b…
Dre Day
  • 338
  • 2
  • 8
0
votes
0 answers

Except clause is not allowed in Jet SQL

I am working on a query that will compare these two data sets and only pull the data that is in the data set one and not in the other. My issue is that Jet SQL in access does not recognize the except clause. Is there an easy substitute for this…
higgins
  • 3
  • 1
0
votes
1 answer

How to give 'where' condition in select query while selecting the columns?

I want to give condition in a column selection while performing the select statement. I want to perform average of TOTAL_TIMEONSITE, RENAME IT, and want to average it for the values existing in the month of Jun'20, Jul'20 and Aug'20 against a…
Muskan Chouhan
  • 3
  • 3
0
votes
1 answer

Access SQL append latest records to historical table

I have this access SQL append query where I have a Primary key set to now allow duplicates in the destination table. I'm confused if I am accomplishing the right thing here with the WHERE condition. I am trying to only screen the newest records from…
Dre Day
  • 338
  • 2
  • 8
0
votes
2 answers

Using MS Access SQL, How Do I Delete Rows With Duplicate Values In Only One Column?

Using MS Access SQL, I am trying to figure out how to delete rows with duplicate values in one column. Example Data: Department | Job Category | Name | Requestor ID Medical Affairs | Vice President | Kim | 123 Medical Affairs |…
Kristin Wong
  • 13
  • 3
0
votes
2 answers

MS Acces Jet SQL Error: Join Expression not supported with multiple Join conditions

I'm trying to run this SQL Expression in Access: Select * From ((TableA Left Join TableB On TableB.FK = TableA.PK) Left Join TableC On TableC.FK = TableB.PK) Left Join (SELECT a,b,c FROM TableD WHERE b > 1) AS TableD …
norix92
  • 1
0
votes
1 answer

How to optimize the SQL query with the subquery?

I want to improve the performance of a SQL query. I have the table 'tblEntries' with the column 'sTag': +----+------+-------+------+---------+ | Id | sTag | sPath | lVer | bActive | +====+======+=======+======+=========+ | 1 | NULL | t1 | 100 |…
John
  • 17
  • 4
1
2
3 4 5