Jet-SQL is an SQL dialect used in Microsoft Access.
Questions tagged [jet-sql]
61 questions
11
votes
3 answers
Where can I find a complete reference for Microsoft Access SQL?
I am looking for a definite reference of the SQL as understood by Microsoft Access. All the links I can find talk only about bits and pieces. Ideally I am looking for a grammar specification with details of what all the different keywords…
Jakob Egger
- 11,981
- 4
- 38
- 48
5
votes
2 answers
Why is this very simple SQL query failing in MS Access?
I have a query that by all rights should not possibly fail, and I can't for the life of me figure out why
INSERT INTO Grocery_Store_Prices(Store,Item,Brand,Price,Unit,Quantity,Note)
VALUES("Kroger","Cheesy Poof","Cartman",0.51,"fart",15,"what is…
BobMcGee
- 19,824
- 10
- 45
- 57
4
votes
2 answers
What is the proper way to refer to the SQL dialect used by Microsoft Access?
I'm converting some SQL that came from Microsoft Access into T-SQL, and I'm making note of these changes in my documentation. This has led me to ask, what is the proper way to refer to the SQL extension used by Access?
I've seen casual references…
EJ Mak
- 799
- 1
- 8
- 29
4
votes
2 answers
ADO is truncating Excel data
I have a function that gets an ADODB recordset from the contents of a worksheet using ADO, as follows:
Function WorksheetRecordset(workbookPath As String, sheetName As String) As adodb.Recordset
Dim objconnection As New adodb.Connection
Dim…
sigil
- 9,370
- 40
- 119
- 199
2
votes
2 answers
MS Access SQL - Capture changes in status over time
I have an Access 2007 database that tracks document progression through time. The progression goes like:
Created
Sent for Review
Reviewed
Sent for Approval
Approved
I've created a history table for changes in document status with columns like…
Tommy O'Dell
- 7,019
- 13
- 56
- 69
2
votes
1 answer
Update Query Timeout on SQL Server View with MS Access
I am having some trouble with updates to an SQL Server View through MS Access. The set of tables used for this is built off of a base table. This base table is of this format.
Id int (not-nullable; auto-assigned)
A1 varchar(50) (nullable)
A2 …
Nathan M.
- 286
- 1
- 9
2
votes
1 answer
sql subqueries, grouping and a bit of math
this counts all items in B and groups on A:
SELECT A, Count(*) AS [Count All]
FROM MyTable
GROUP BY A;
this counts all 1s in B and groups on A:
SELECT A, Count(*) AS [Count Ones]
FROM MyTable
WHERE
MyTable.[B]='1'
GROUP BY A;
How do I put…
Daniel
- 34,125
- 17
- 102
- 150
2
votes
2 answers
Access Database - SQL, Filtering by Multiple Items in Same Column
I'm trying to create a SQL query in Access that will return all fields (SELECT *) when KEY is associated with three over the counter drugs (Motrin, Tylenol, and Bayer). In the example below, all rows for AMYZ32874 would be returned and none of the…
Ollie
- 337
- 3
- 6
- 17
1
vote
1 answer
Not exists element while also multiple join criteria
I want to find all entries of a table which have another entry of a certain property and do not have another entry, not with this property. And to connect entries I need to look at two non-unique fields.
A simplified version of data is like…
Simonsen
- 11
- 1
1
vote
1 answer
How to get MS Access to group by date column
I'm having difficulty grouping by date in Microsoft Access. Any guidance would be great. Thank you.
Fake Table - schedules:
Date
Location
Appointment ID
People
1/3/23
East
98765
3
1/4/23
West
983746
2
1/3/23
East
09382
5
Query I'm…
dat.a.tho
- 59
- 7
1
vote
1 answer
Is there a JET SQL condition clause to update only the Max Date records?
I am using this Jet SQL Syntax,
UPDATE tbl1
INNER JOIN tbl2 ON tbl1.123 = tbl2.123
SET tbl1.a = tb2.b
For my condition to update only the max dates for table one I have used iterations of:
WHERE tbl1.123 IN (SELECT tbl1.123 FROM tbl1 GROUP…
Dre Day
- 338
- 2
- 8
1
vote
1 answer
Using "or" in my SQL query crashes MS Access 2019
This is my first question on Stack.
I am a student trying to develop my querying abilities through practise. Using Microsoft Access and some publicly available census data (5 tables of 57,000 rows) I came across the following issue.
When I try to…
user19218445
1
vote
2 answers
How to calculate the difference of two SELECTs in SQL (Access, JetSQL)
I need to get the difference of two SUM...WHERE queries from the same table join:
SELECT SUM(Service_template.Service_fee)
FROM (Service_template LEFT JOIN Service ON Service_template.Service_Code = Service.Service_Code) LEFT JOIN Bill ON…
K307
- 105
- 7
1
vote
1 answer
What is the MS Access SQL equivalent of FULL OUTER JOIN with a.key IS NULL and b.key IS NULL
Example Query that I want to execute in MS Access SQL:
SELECT *
FROM TableA AS a
FULL OUTER JOIN TableB AS b
ON a.key = b.key
WHERE a.key IS NULL
OR b.key IS NULL
Since MS Access SQL does not allow FULL OUTER JOIN, I tried using the code below but…
Kristin Wong
- 13
- 3
1
vote
1 answer
Run one MS Access SQL script on a particular Table chosen by user
I have a MS Access 2016 database (*.accdb) with 20+ Tables. Fields in each of them vary slightly from Table to Table. I've no VBA experience, so I'm sticking only to the SQL query below (redacted).
SQL script
myvar below is the parameter I'd like to…
mlomailom
- 127
- 1
- 9