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


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

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 EXISTS predicate: SELECT * FROM YourTable AS t1 WHERE EXISTS ( SELECT * FROM YourTable AS t2 WHERE t2.Name = t1.Name AND ...


7

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


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


7

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')


7

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 by inserting in the tables on the 1-side of the relation (the master or parent or primary key table). Then only you can enter a record on the n-side of the ...


6

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


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

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

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 in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp. 4.Under login information, choose a blank username and be ...


5

You can write a sub to backup the database to a specified folder and run this sub when the database is launched. There are a lot of ways to do this, but I personally use the following: Function fMakeBackup() As Boolean Dim Source As String Dim Target As String Dim retval As Integer Source = CurrentDb.Name Target = "Z:\My Apps\Backups\...


5

As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include: Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the ...


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

MS Access Vs MySQL MS Access works only on Windows whereas MySQL works on almost all platforms. MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support. MySQL is opensource so no cost involved. Performance in MySQL is way better than MS Access MS Access database is more suitable for desktop use with a small ...


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


5

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


5

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


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

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


5

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 then they can/should not be able to corrupt your data. About business rules. According to Wikipedia: A business rule is a rule that defines or constrains some ...


5

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 / total.fSum ; As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) ...


5

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 by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in ...


5

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 perspective, something is something regardless of where in the model you use it. The entry looks pretty decent: CREATE TABLE entry ( EntryId ... NOT NULL PRIMARY KEY , ...


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