10

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 ListA....


10

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 ...


9

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 ...


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] ...


7

SELECT col1, col2, col3, col4, col5, col6 FROM TableX WHERE col1 = 1 OR col2 = 2 OR col3 = 3 ORDER BY (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) + (CASE WHEN col2 = 2 THEN 1 ELSE 0 END) + (CASE WHEN col3 = 3 THEN 1 ELSE 0 END) DESC, col4, col5, col6 or, for MS-Access: ORDER BY IIF(...


7

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 ...


7

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 ...


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 ...


7

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 columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing). I see two options depending on what your ...


7

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 designed so that it respects SQL Server constraints and rules. Also how will your Application stop users from making simple mistakes, like deleting records if they'...


6

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.


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 characteristic ...


6

Try: NZ(MaritalStatus,"SINGLE") From Nz Function in the documentation: You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression. Or you can use: IIF(...


6

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', '...


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

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 a given Period ( defined by the composite key of Year-Week ) User-Actions taken during an unknown Period My initial impression is that the unknown period must ...


6

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.ID, a.job, b.job FROM a LEFT JOIN b ON (a.ID=b.ID and b.lang='de')


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

You can either build your own monitoring tool or look to a 3rd party solution that can provide one for you. If you're interested in building your own, it depends on what version of SQL Server you're working with. If it's 2005, you can use the Blocked Process Report trace event. If you're running 2008 or above, I'd suggest using the equivalent extended event, ...


5

First you will have to use query language. All the flavors of SQL use the almost same SQL query syntax. So you will first have to learn SQL query language. For eg. you can start with tutorial at www.w3schools.com/sql/default.asp Stanford University had offered public course on databases last Fall. They had nice tutorial on how to set up SQLite. Once you ...


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

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

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

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

(Borrowing from my earlier answer here...) re: actually performing the backup Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like copy /Y d:\apps\databases\...


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

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 ...


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