All Questions
15,722
questions
189
votes
9
answers
724k
views
How to do INSERT into a table records extracted from another table
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So ...
- 21.2k
147
votes
9
answers
1.2m
views
How to SUM two fields within an SQL query
I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.
This is my code.
Sum(tbl1.fld1 + tbl1.fld2) AS [Total]
Is this ...
- 1,895
121
votes
6
answers
430k
views
delete all from table
what's faster?
DELETE FROM table_name;
or
DELETE FROM table_name where 1=1;
why?
does truncate table work in access?
- 59.6k
89
votes
15
answers
168k
views
How to find longest string in the table column data
I've a table contains the columns like
Prefix | CR
----------------------------------------
g | ;#WR_1;#WR_2;#WR_3;#WR_4;#
v | ;#WR_3;#WR_4;#
j | WR_2
m ...
- 1,656
89
votes
4
answers
185k
views
How can I convert an MDB (Access) file to MySQL (or plain SQL file)?
Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don't have to go through the CSV step.
I ...
- 86.5k
87
votes
16
answers
192k
views
SQL Query - Using Order By in UNION
How can one programmatically sort a union query when pulling data from two tables? For example,
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY ...
- 4,970
86
votes
3
answers
101k
views
SQL multiple join statement
When I had only one inner join in my SQL statement, it worked perfectly. I tried joining a second table, and now I am getting an error that says there is a syntax error (missing operator). What is ...
- 1,593
84
votes
11
answers
148k
views
How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
I use the following code in Python (with pyodbc for a MS-Access base).
cursor.execute("select a from tbl where b=? and c=?", (x, y))
It's Ok but, for maintenance purposes, I need to know the ...
- 3,352
56
votes
2
answers
122k
views
Multiple INNER JOIN SQL ACCESS
Syntax Error (missing Operator) in query expression 'tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - ...
- 595
52
votes
12
answers
58k
views
SQL: Using NULL values vs. default values
What are the pros and cons of using NULL values in SQL as opposed to default values?
PS. Many similar questions has been asked on here but none answer my question.
- 3,060
49
votes
6
answers
113k
views
Date Difference between consecutive rows
I have a table with following structure
ID Account Number Date
1 1001 10/9/2011 (dd/mm/yyyy)
2 2001 1/9/2011 (dd/mm/yyyy)
3 2001 3/9/...
- 659
48
votes
2
answers
75k
views
Why is "Yes" a value of -1 in MS Access database?
I'm looking at linked data in MS Access.
The "Yes/No" fields contain the value -1 for YES and 0 for NO. Can someone explain why such a counter-intuitive value is used for "Yes"? (Obviously, it should ...
- 2,399
46
votes
7
answers
159k
views
Using left join and inner join in the same query
Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and ...
- 1,134
46
votes
4
answers
285k
views
How to create a new column in a select query
In MS Access, I want to insert a new column into the returned result of a select query. The new column has the same value for every row. For example, my select returns columns A, B and I want C to be ...
- 21.2k
45
votes
17
answers
69k
views
Editing Record issues in Access / SQL (Write Conflict)
a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another ...
- 3,014
38
votes
6
answers
160k
views
SQL to Query text in access with an apostrophe in it
I am trying to query a name (Daniel O'Neal) in column names tblStudents in an Access database, however Access reports a syntax error with the statement:
Select * from tblStudents where name like '...
- 467
38
votes
8
answers
79k
views
coalesce alternative in Access SQL
In T-SQL, you can do this:
SELECT ProductId, COALESCE(Price, 0)
FROM Products
How do you do the same thing in Access SQL? I see examples for doing it with Nz in VBA, but I'm looking for the SQL ...
- 6,541
36
votes
3
answers
102k
views
Case expressions in Access
Can you use case expressions in Access? I'm trying to determine the max date form 2 columns but keep getting syntax errors in the following code:
CASE
WHEN dbo_tbl_property.LASTSERVICEDATE > ...
elphj
36
votes
8
answers
80k
views
How do I create a table alias in MySQL
I am migrating an MS Access application (which has linked tables to a MSSQL Server) to MySQL.
As a means to overcome some MSAccess table naming problems, I am seeking a solution to add a MySQL table ...
- 3,288
35
votes
7
answers
29k
views
Lightweight SQL database which doesn't require installation [closed]
Could you recommend a lightweight SQL database which doesn't require installation on a client computer to work and could be accessed easily from .NET application? Only basic SQL capabilities are ...
- 34.2k
34
votes
2
answers
110k
views
How can I join two tables but only return rows that don't match?
I have two tables which look like this:
T1: ID | Date | Hour | Interval
T2: ID | Date | Hour
I basically need to join these tables when their IDs, dates, and hours match. However, I only ...
- 2,494
34
votes
5
answers
44k
views
Export all MS Access SQL queries to text files
I have to document an MS Access database with many many macros queries, etc. I wish to use code to extract each SQL query to a file which is named the same as the query, eg if a query is named ...
- 353
33
votes
3
answers
254k
views
How do I count unique items in field in Access query?
My Table: table1
ID Name Family
1 A AA
2 B BB
3 A AB
4 D DD
5 E EE
6 A AC
SQL command on Access:
select count(*) from table1
Output: ------------> True
6 row(...
user1526510
32
votes
4
answers
105k
views
How do I write a full outer join query in access
Original query:
SELECT *
FROM AA
FULL OUTERJOIN BB on (AA.C_ID = BB.C_ID);
How do I convert the query above to make it compatible in Microsoft Access?
I am assuming:
SELECT *
FROM AA
FULL LEFT ...
- 321
32
votes
4
answers
116k
views
Ms Access Query: Concatenating Rows through a query
Suppose I have table in Ms Access with following information:
ColumnA ColumnB
1 abc
1 pqr
1 xyz
2 efg
2 hij
3 asd
My question is, how can I concatenate the values ...
- 13.6k
31
votes
5
answers
90k
views
How to delete in MS Access when using JOIN's?
I am attempting to use the DELETE clause in MS Access and have an issue when also using the JOIN clause. I have notice this can be accomplished by using the DISTINCTROW key word.
For example, the ...
- 4,970
30
votes
2
answers
30k
views
What is the difference between MSSQL and TSQL?
MSSQL and T-SQL are often thrown around as interchangeable synonyms on the web.
I know that T-SQL is a flavor of SQL used in many Microsoft products. Is MS-SQL actually another flavor of Microsoft ...
- 1,260
29
votes
8
answers
51k
views
[] brackets in sql statements
What do the brackets do in a sql statement?
For example, in the statement:
insert into table1 ([columnname1], columnname2) values (val1, val2)
Also, what does it do if the table name is in ...
- 3,389
29
votes
7
answers
23k
views
Table-less UNION query in MS Access (Jet/ACE)
This works as expected:
SELECT "Mike" AS FName
This fails with the error "Query input must contain at least one table or query":
SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName
Is this ...
- 24k
29
votes
10
answers
87k
views
Automatically Generate SQL from existing MS Access table
I've just designed a large database by playing around in MS Access. Now that I'm happy with the design, I need to be able to generate this same database (tables, relationships, etc.) from code.
...
- 58.9k
28
votes
5
answers
78k
views
How do I comment SQL code out in Microsoft Access?
Is it possible to comment code out in the SQL window in Microsoft Access?
- 59.6k
27
votes
2
answers
14k
views
How do I use parameters in VBA in the different contexts in Microsoft Access?
I've read a lot about SQL injection, and using parameters, from sources like bobby-tables.com. However, I'm working with a complex application in Access, that has a lot of dynamic SQL with string ...
- 32.2k
25
votes
10
answers
66k
views
Escaping ' in Access SQL
I'm trying to do a domain lookup in vba with something like this:
DLookup("island", "villages", "village = '" & txtVillage & "'")
This works fine until txtVillage is something like Dillon's ...
- 3,337
25
votes
4
answers
64k
views
Insert record into table if entry does not exist in another table- with an extra twist
Hi to all you mighty SQLsuperheros out there..
Can anyone rescue me from imminent disaster and ruin?
I'm working with Microsoft Access SQL. I'd like to select records in one table (table1) that don't ...
- 361
25
votes
2
answers
119k
views
Using Excel VBA to export data to MS Access table
I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table.
Public Sub TransData()
...
- 285
24
votes
4
answers
51k
views
Why does a LIKE query in Access not return any records?
Is there any reason why
SELECT * FROM MyTable WHERE [_Items] LIKE '*SPI*'
does not return any records with OleDbAdapter.Fill(DataSet) or OleDbCommand.ExecuteReader()?
When I run the same SQL in MS ...
- 11.3k
24
votes
4
answers
152k
views
MS Access - execute a saved query by name in VBA
How do I execute a saved query in MS Access 2007 in VBA?
I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query.
This doesn't work ... VBA can't find the query....
- 2,441
23
votes
3
answers
183k
views
"You tried to execute a query that does not include the specified aggregate function"
SELECT SUM(orders.quantity) AS num, fName, surname
FROM author
INNER JOIN book ON author.aID = book.authorID;
I keep getting the error message: "you tried to execute a query that does not include the ...
- 1,774
23
votes
2
answers
120k
views
UPDATE statement with multiple WHERE conditions
I'm trying to run an UPDATE query in Access with different WHERE conditions:
UPDATE Table1
SET [Ticker] = "TXSFI" WHERE [Acct Numb] like "*03",
SET [Ticker] = "TESEI" WHERE [Acct Numb] like "*04";
...
- 233
23
votes
4
answers
26k
views
is there a group_concat function in ms-access?
is there a group_concat function in ms-access or something similar?
- 59.6k
22
votes
10
answers
51k
views
Linked Access DB "record has been changed by another user"
I'm maintaining a multiuser Access 2000 DB linked to an MSSQL2000 database, not written by me.
The database design is very poor, so you'll have to bear with me.
On the 'Customer' form there's a '...
- 1,378
22
votes
4
answers
148k
views
where to place CASE WHEN column IS NULL in this query
I'm having some trouble translating an MS Access query to SQL:
SELECT id, col1, col2, col3
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
LEFT OUTER JOIN table3
ON table1.id = table3....
- 359
22
votes
2
answers
269k
views
A select query selecting a select statement
I don't even know if I am doing this query the right way.
There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread).
So I made a query that combines all of the ...
- 7,978
21
votes
6
answers
92k
views
How can I implement SQL INTERSECT and MINUS operations in MS Access
I have researched and haven't found a way to run INTERSECT and MINUS operations in MS Access. Does any way exist
- 7,167
21
votes
3
answers
22k
views
Do we have transactions in MS-Access?
I am developing a small desktop application using C#.NET and MS-Access. I don't have any prior experience of MS-Access. I want to know if we can use transactions in Ms-Access or not.
I have the below ...
- 34.2k
20
votes
8
answers
67k
views
Check if access table exists
I want to log web site visits' IP, datetime, client and refferer data to access database but I'm planning to log every days log data in separate tables in example logs for 06.06.2010 will be logged in ...
- 13k
20
votes
16
answers
119k
views
How to List Field's Name in table in Access Using SQL
Can you please let me know if it is possible to list all fields name in a MS Access table?
- 5,774
20
votes
6
answers
26k
views
Table Creation DDL from Microsoft Access
Is there any easy way to retrieve table creation DDL from Microsoft Access (2007) or do I have to code it myself using VBA to read the table structure?
I have about 30 tables that we are porting to ...
- 2,823
20
votes
6
answers
56k
views
can we list all tables in msaccess database using sql?
Can we find all tables in the msaccess using sql .
as we do in sqlserver
select * from sys.tables
in sqlite
SELECT * FROM sqlite_master where type='table'
- 10.6k
20
votes
2
answers
93k
views
How to save the result of a SQL query into a variable in VBA?
I want to execute a select statement and put the result of it (which is only 1 record with 1 value) in a variable.
This is in VBA code in access.
Private Sub Child_Click()
Dim Childnummer As ...
- 464