New answers tagged


As I stated in a comment above, you'll want to re-enable a primary key. If you already have a filled table, you may have to migrate it to a new table, as - from what I recall - Access doesn't like creating autonumber fields in existing tables that don't currently have them. So, create a new Query, ignore the design view and click over to SQL view (close ...


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


You can use a default date of something way out of bounds. e.g. in the table definition YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99 That way you know that everything gets entered with some date. It will continue to not allow you to enter in duplicate data. In addition, you can create an exception report based off these values kicked off to someone ...


You could do a lot of stuff but integer and text do not sort the same. Text would break a search on week > 6. In text 10, 11, 12 are not > 6. I would use 0 for no date Not that size is the big of a deal but you would use tinyint for week and smallint for year one varchar is the size of a smallint




want to set the CMS.DES to 'USD' when CMS.DES IS NULL else CMS.DES isnull(CMS.DES, 'USD') isnull


The last three derived tables in your query are almost identical: they join the same tables in the same manner and use almost identical filters. The only difference is the cr.type condition: each subquery filters on a different value of that column. In such circumstances, you can easily combine those three derived tables into one producing all three counts. ...


Is there an obvious flaw in the code? My best estimation is looking at the coalesce function - ... Yes to both. One problem is that the 2nd (and 3rd) FULL JOIN use the (derived) tables' group_name columns. As a result, the 2nd and 3rd full join cancel the 1st and the 2nd full join respectively. Your query with only the basic structure, stripped from the ...


You have cr.type in some of your group by. You need to remove this to avoid getting duplicates. I do not see this in the select lists so I dont think you need it in the group by.


There is a mismatch between you and your DBMS regarding special values. Special values are special in your mind, but the DBMS can't tell that a special value is different from an ordinary value. So when you put a special value in, it expects a valid reference to a row in the lookup table. If the reference is invalid, it flags a constraint error. There is ...


As already cited by others in comments, adding NOT NULL to your table specification can improve in a significant way the performances of your queries (in addition to the very good methodological reasons stated in another answer). The reason is that the query optimizer, knowing that a column cannot have a NULL value, can exclude special tests for such values,...


What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL. Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors. IMHO it is always best to ...


There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, Login I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider ...


Select FullName, Login FROM [User] ORDER BY CASE WHEN FullName IS NULL THEN 'ZZZZZZZ' ELSE FullName END, login asc

Top 50 recent answers are included