Hot answers tagged

9

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 IIF field selections might benefit from using a Switch statement instead. It seems to be sometimes the case, especially with things SQL, that a SWITCH (more ...


8

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(IsNull(x), y, x)) can then be written as: CASE WHEN x IS NULL THEN y ELSE x END and simplified further to COALESCE(x, y). The table-column expressions: [tab]![col] ...


8

The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g. SELECT ListA.*, ListB.* FROM ListA LEFT JOIN ListB ON ...


7

Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free: alter database [...] set read_committed_snapshot on; No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of ...


7

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: SELECT T1.*, (SELECT T3.SMN_DATEC-1 FROM (SELECT T2.DET_NUMBERA , T2.SMN_DATEC , row_number() over (ORDER BY ...


6

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 specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options: [INNER] JOIN: ...


6

Charles, You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system. If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational ...


6

Here is Microsoft's page about Access SQL syntax: http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx and here is a tutorial about Access SQL syntax for Access 2000. A bit dated, but the concept is still there: ...


6

You need to use an outer join, otherwise you won't get those students back that did not borrow a book. Then you need to count() on the students table, not "the group" (which is done when you use (*)) SELECT LEERLINGEN.LLNR, LEERLINGEN.VOORNAAM, LEERLINGEN.TUSSENVOEGSEL, LEERLINGEN.ACHTERNAAM, LEERLINGEN.KLAS, COUNT(LEERLINGEN.LLNR) ...


6

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are: Data Integrity Query Flexibility The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each ...


5

The only issue here is that MS Access has no idea what a CASE expression is. Instead, you need to use IIF, e.g.: IIF(Credit-Debit > 0, Credit-Debit, NULL) AS Credit, IIF(Credit-Debit < 0, Credit-Debit, NULL) AS Debit, BTW IIF is now supported in SQL Server 2012.


5

After translation of table names i think that this will work: SELECT LEERLINGEN.LLNR, LEERLINGEN.VOORNAAM, LEERLINGEN.TUSSENVOEGSEL, LEERLINGEN.ACHTERNAAM, LEERLINGEN.KLAS FROM LEERLINGEN LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR WHERE UITLENINGEN.LLNR IS NULL; EDIT: You are looking for LEERLINGEN ...


5

When you have an aggregate function, you need a GROUP BY statement. In your case, it would be SELECT LEERLINGEN.LLNR, UITLENINGEN.LLNR, LEERLINGEN.VOORNAAM, LEERLINGEN.TUSSENVOEGSEL, LEERLINGEN.ACHTERNAAM, SUM(UITLENINGEN.BOETE) FROM LEERLINGEN INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR WHERE ...


5

A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you. Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.


5

I'll just summarize all my comments here as an answer. You should read this: http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.105%29.aspx#Use_startup_accounts The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since ...


5

MS-Access is rather picky at how it wants the joins to be formed. Add parentheses: SELECT A.Name, B.Quantity, C.Quantity FROM ( A LEFT OUTER JOIN B ON A.ID = B.ID ) LEFT OUTER JOIN C ON B.No = C.No ; Standard SQL syntax - and most other DBMS - do not need require parentheses in the FROM clause, although you are allowed to use them for clarity.


5

You have a working query, but you are selecting: FROM CF30, EC01, OC02, OM01U1, RS2101F With no explicit joins and only one implicit join: WHERE OM01U1.OM01015 = RS2101F.OUTNUM This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say: FROM OM01U1 INNER JOIN RS2101F ON OM01U1.OM01015 = ...


5

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you: Sample tables and data CREATE TABLE SupplierCountry ( SupplierName varchar(50) NOT NULL, CountryName varchar(50) NOT NULL ); INSERT SupplierCountry (SupplierName, CountryName) VALUES ('Supplier A', 'USA'), ('Supplier A', ...


5

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 returned by the inner most nested query are primary key values (scans) that are used to retrieve additional flank attributes (lat and lon) using a seek for final ...


5

This sounds like an enormous project. My advice is to go back and look again for an open source, off-the-shelf product that meets your main needs. It is difficult to believe that your school is alone in its needs and that no-one else has similar requirements. Even if you the system does not do everything you require, as long as it is built in an open way, ...


5

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery. If I edit the linked Access table will it be reflected in the SQL server table? Yes; as before, these updates will be visible on the other end on next refresh or requery. Trusted Connection only controls how the connection ...


4

We can't really answer whether you should consider other platforms such as mongodb - we know nothing about the application or its data and whether such a platform would be appropriate. I can tell you that SQL Server has a lot more features geared toward performance and tuning than Access will ever have. Access is pretty simplistic and, while it can ...


4

Try this: SELECT a.MaxOfShipID, a.Serial_number, a.[Recent Date], a.[Send_Recieve] FROM <tablename> a INNER JOIN ( SELECT serial_number, MAX([Recent Date]) AS [Recent Date] FROM <tablename> GROUP BY serial_number ) b ON a.serial_number = b.serial_number ...


4

I would use a view. Just prepare a select in the form of: SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6 FROM Table1 UNION SELECT Field1, null, Field3, Field4, Field5, Field6 FROM Table2; You can refine the select to filter or extend, as you like. When you're satisfied, just define a view as: CREATE VIEW MergedTable AS ...


4

Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a ROWVERSION column to these tables. One potential issue, though, with merge replication specifically, is if Access ...


4

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords. Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their ...


4

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, correct?) Then I would just copy and paste the core dataflow and make per-table changes as appropriate. For a non-repeating process that is a small amount ...


4

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 Date for each Food Item. SELECT FPD1.[Food item ID] AS ItemID ,MAX(I1.[Invoice Date]) AS MaxDate FROM [Food purchase data] AS FPD1 INNER JOIN ...


4

I think this one needs a subquery, although someone else may prove me wrong. Lets take it step-by-step: First, lets get the genre of every movie in GenresMovies, along with the description: -- query 1 -- SELECT gm.GenreID, g.GenreDescr FROM GenresMovies gm, Genres g WHERE gm.GenreID = g.GenreID; Note this is an alternate syntax for performing an ...



Only top voted, non community-wiki answers of a minimum length are eligible