12
votes
SQL Database vs Multiple Microsoft Access Databases
In my opinion, Microsoft Access is an obsolete way to manage data, especially within an organization. There's a lot of limitations and lack of features compared to a modern database system, besides ...
- 35.2k
10
votes
Accepted
Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA
I must first compliment you on your courage to do something like this with an Access DB, which from my experience is very difficult to do anything SQL-like. Anyways, on to the review.
First join
Your ...
- 1,320
8
votes
How do you translate this into SQL Server 2008 statement?
Instead of rewriting the whole query, I'll give you some hints of how to do it:
IIf(condition, a, b) becomes: CASE WHEN condition THEN a ELSE b END
IsNull(x) becomes: x IS NULL
The combined IIf(...
- 97.4k
8
votes
Accepted
Compare Two Rows and Find the Difference Between Them Which is In The Second Column?
You could rephrase it like this:
Return every row for which another row exists with the same Name and a different LastName
which is very easy to translate to SQL if you are familiar with the ...
- 22.8k
7
votes
Accepted
SQL queries to only show most recent purchase records for individual food items
MS Access is rather limited.
I assume that it is possible to have more than one invoice for the same date.
In this case I'll pick an invoice with the highest ID.
At first we'll find maximum Invoice ...
- 4,577
7
votes
Error at line 1, column 25: ORA-00923: FROM keyword not found where expected
As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes:
...
- 331
7
votes
Non unique one to many relationship
What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the ...
- 97.4k
7
votes
Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?
Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past.
No, Access itself won't corrupt data. However your Access application needs to be ...
- 3,203
7
votes
Accepted
Easiest way to import access db (.accdb) into SQL Server 2016?
1.Open MS SQL Server Import and Export Wizard.
2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.
3.Click “Properties” enter the location of the .accdb file ...
- 493
7
votes
LEFT JOIN with WHERE (Microsoft Access SQL)
In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:
SELECT a....
- 318
7
votes
Accepted
What principles should I follow when entering data into a complex relational database?
A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start ...
- 1,213
6
votes
Accepted
What is the meaning of this operator: *=
Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then ...
- 68.3k
6
votes
How can I allow part of a key to be null and enforce uniqueness of records with nulls?
The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.
User-Actions taken during ...
- 4,586
6
votes
Accepted
Bypass MS Access 2GB file size limitation
You can't bypass the built-in limitation of an Access database. You can however circumvent the built-in limitation by using built-in features like splitting the database objects between multiple *....
- 17.4k
5
votes
Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA
Added additional attributes and filter conditions.
Any form of cross join is eliminated by using min and max nested queries. This is the biggest performance gain.
The min and max flank values ...
- 698
5
votes
Accepted
Loop through tables or copy paste
Given that:
There are some columns you don't want to pull through for each table
Column names are different for each table
You only have 40 tables
This is a one-time migration (this is safe to assume,...
- 68.3k
5
votes
Accepted
Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?
If they have access to the tables directly with insert, update and/or delete rights then they can corrupt your data. If you prevent this by limiting the access-rights or by an MS-Access application ...
- 3,700
5
votes
Accepted
Fill percentage of total field with MS Access SQL
You need UPDATE, not INSERT:
UPDATE MyTable
SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;
or:
UPDATE
MyTable AS my,
(SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / ...
- 97.4k
5
votes
why the last function in msaccess allow me to bypass the group by?
What is the behaviour of the LAST function?
From the documentation:
These functions return the value of a specified field in the first or last record, respectively, of the result set returned ...
- 83k
5
votes
What principles should I follow when entering data into a complex relational database?
Some general advice. In the image, I guess Keyword_Priority is dependent on Keyword which is dependent on Entry. You, therefore, need to start with Entry, then Keyword, etc.
From a modelling ...
- 23.1k
5
votes
SQL Database vs Multiple Microsoft Access Databases
Education: full-featured databases (which Access is not) have a thing called Indexes*. When configured, these allow you to pull data by indexed values without visiting the rest of the table. You can ...
- 1,206
4
votes
Accepted
Query that compares dates
Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that ...
- 9,095
4
votes
Non unique one to many relationship
What you appear to have is two tables that refer to a common field
They have an inferred relationship
In the case of a common field then create a master table for Field1 that is nothing but the ...
- 5,043
4
votes
EXEC xp_dirtree
The undocumented Stored Procedure xp_dirtree requires the sysadmin fixed server role in order to see any files when the procedure is called.
Adding this role to your user test should enable you to ...
- 678
4
votes
Fill percentage of total field conditional on some other field with MS Access SQL
Your last UPDATE statement should work. Access is often a bit crap though so although it should work, it probably won't. Thanks Microsoft Access dev team (probably more specifically the updates to JET)...
- 1,621
4
votes
Easiest way to import access db (.accdb) into SQL Server 2016?
For me it didn't work to try to import directly from the ACCDB file. I had to save in Access as an mdb file and do the import that way.
- 41
4
votes
Compare Two Rows and Find the Difference Between Them Which is In The Second Column?
An alternative way, under the assumption that there are no repeated (Name, LastName) pairs, is the following.
First select all the names that appear more than once, and create a Query out of them.
...
- 13.3k
4
votes
Accepted
How to compact the current MS Access database from VBA function
This is simply not possible. Compacting and repairing a database requires the database to be closed. As such, you can't compact and repair a database between steps in a sub or procedure, since the ...
- 248
4
votes
Accepted
Database software solution
SQL Server express is a good choice. You can also create a very low cost (a few bucks a month) database only on Azure. In that scenario there would be no maintenance. I would argue SQL is what you ...
- 104
4
votes
Accepted
Is it better to have multiple tables with the same primary key or one big data table?
As per mustaccio's comment, when you have the same PrimaryKey defined in each of your Tables, that then guarantees a one-to-one relationship is enforced between each Table. In other words, based on ...
- 35.2k
Only top scored, non community-wiki answers of a minimum length are eligible