Stack Overflow
Stack Overflow

Questions tagged [jet-sql]

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

61 questions
0
votes
1 answer

Change SQL Server Pass Through Query Variable

So I have a SQL Server pass through query in MS-Access that I created with the query design. I just double click it and it runs and opens up in datasheet view. Then I can export it. The query looks like so: DECLARE @acyr AS varchar(4); SELECT @acyr…
honey_badgerzz
  • 115
  • 8
0
votes
1 answer

How to update a string by inserting characters in between

In MS-Access, I have a text field labeled idStr and currently, the data shows like this "000000000". I would like to transform the string by writing an update query and making it like this: "000-00-0000". In other words, I'd like to insert -…
honey_badgerzz
  • 115
  • 8
0
votes
1 answer

Changing Jet SQL IsNull to to SQL IsNull Function

I have a query in MS Access that I am trying to change to SQL view One of the select statement part is IIf(IsNull([Book ID]),-1,[Book ID]) AS SubBookID Unlike in Access T-SQL wants 2 parameters for the IsNull function. What I need to do is…
jedu
  • 1,211
  • 2
  • 25
  • 57
0
votes
1 answer

UniqueID from three fields and retrieve data

I have one table, 10 fields(f1-f10). Combining f1-f2-f3 gives me a new field. I would like to retrieve all data from table, for distinct values of this new field(f1-f2-f3). Running sub-queries, but getting all records. Please suggest. vik
user9087288
0
votes
2 answers

COUNT, IIF usage for counting records that also have a specific field value matched

Using MS Access and I have two tables, one is categories and the other is content. My initial SQL statement, included below,takes a count of each content associated to a category and returns the count associated with each category. So for each…
VanCoon
  • 422
  • 4
  • 20
0
votes
1 answer

MS Access Datediff with more than one date1 and single date2 argument

Assume the following: (Disregard the bad sql syntax in "create table" segments, I'm simplifying the message for better understanding) Create table Event_Participants ( Event_Name, text Event_Participant, text Event_Participant_Arrival,…
Nikola Pavlovic
  • 91
  • 10
0
votes
1 answer

MS Access\Jet SQL - staff clock in & out times, same table, sum full time worked per staff

I have the below table named 'timings'. It holds the comings and goings of staff members (morn_in, morn_out, afternoon_in, afternoon_out). timings table What I would like to achieve is an output grouped by staff_ref, SUM of their hours worked,…
the_cockerel
  • 17
  • 4
0
votes
1 answer

Jet SQL/MS Access - Query, count of tasks, sum of time, by user

I would really appreciate help in achieving the correct output, I want to understand how it works and self teach. I have one table: I would like to achieve the below results from one query: user_name | task | CountOftask | SumOftime …
the_cockerel
  • 17
  • 4
0
votes
2 answers

SWITCH Incorrect Argument Amount - Can't See Where

I'm trying to use the SWITCH function in MS Access and I keep getting told there aren't the right number of arguments. I'm new to the switch function but I understand the syntax. Switch ( expression1, value1, expression2, value2, ... expression_n,…
Boltie
  • 3
  • 4
0
votes
0 answers

Using ConcatRelated() To combine older data rows into newer rows

Access(2013) SQL I am using ConcatRelated()\Dlist (sometimes I refer to Visual Objects that are sourced) to handle a few task with the table below. I can combine data based on a matching key though what i would like to do is combine data based on a…
Aquarious Workman
  • 1
  • 1
0
votes
1 answer

Single script to insert multiple exceptions

I use below script to insert orders transaction manually. This script processes one order at time (@orderId - using this variable here). I got a list of 200 orders, is there a way i can process all orders using single script? DECLARE …
Ankit
  • 1
  • 1
0
votes
1 answer

MS Access - Remove duplicates based on three columns while keeping one result without using SQL Min/Max

I have been having an issue for a little bit of time now and cannot find a solution that has worked for me. It might be that I am just not doing it correctly or that there is an alternative that will work better. I am opened to, and appreciative,…
ivkovmg
  • 23
  • 5
0
votes
1 answer

(MS ACCESS) SQL to group joined statement

Good Morning! I have an MS Access split database (not using ms sql server (yet)) thats joining a couple of tables with a temp query (imaginatively called "temp"!) SELECT table_CORE.[core-ID], table_CORE.[PO], table_CORE.[Part], table_CORE.[Part…
Callum P. Robertson
  • 23
  • 6
0
votes
1 answer

Can I make the determination of which column I update conditional on something?

Is it at all possible to make the determination of which columns to update in an update statement conditional? I am trying to run an update query to update one of 30 columns per record, but only if the previous field in the record is already filled…
Benjamin
  • 1
  • 1
0
votes
1 answer

MS Access SQL(Jet SQL)| Syntax Error(Missing Estatement)

I am writing a query in MS Access SQL doing some math. SELECT DISTINCT QParts.PartsNumber, SWITCH(([dbo_ICILOC].[QTYONHAND] + [dbo_ICILOC].[QTYRENOCST] + [dbo_ICILOC].[QTYADNOCST] - [dbo_ICILOC].[QTYSHNOCST] - [dbo_ICILOC].[QTYCOMMIT]) IS…
B. Erdenebilig
  • 1
  • 2
1 2
3
4 5