New answers tagged

3

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 see the results. It is also worth repeating what has been mentioned in the comments; As this is an undocumented feature of SQL Server it could be removed in a ...


0

You can use a UNION query to force the Sex1/Sex2 pairs to be in the same order SELECT Nz(Sex1, "") & Nz(Sex2, "") AS Sex, Number FROM TableName WHERE Nz(Sex1, "") >= Nz(Sex2, "") UNION ALL SELECT Nz(Sex2, "") & Nz(Sex1, "") AS Sex, Number FROM TableName WHERE Nz(Sex1, "") < Nz(Sex2, "") ...


-1

You can try using CONCAT() function in MSSQL to get 2 columns in one. SELECT CONCAT(Sex1,Sex2) AS Sex, Sum(Number) AS Total_Number FROM db.test GROUP BY concat(Sex1,Sex2);


0

You could use a CASE expression or the IIf() function to first order the two values and then group by them: with CASE expression: SELECT CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END & CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex, Sum(Number) AS [Total Number] FROM TableName GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE ...


0

Create a form based on that table, and only include the fields you want them to be able to edit. Alternatively, you can add all the fields (for visibility) and disable (in the field Properties) the ones you want to be excluded from edits. They will still be visible on the form, but the data they display cannot be edited.


0

A lookup table with a single value (StateAbbr) should contain two fields: StateId (Primary Key) as an autonumber type, and StateAbbr as a text type. Your Persons table should contain a number type field that will store the value held by the StateId field in the StateAbbr table. So, The query used would be something like: SELECT Persons.StateAbbr, ...


1

Is the front-end locked down (for design) and properly linked to the back-end? If they are, you will need to go back to your development copy, save it on the end-users' machines individually, and re-link to the databases on the file server. i have a VBA script that runs when the front-end starts up that checks for the existence of the backend. if the ...


0

I found the answer. Just change as shown in photo where in between the brackets put the field you need.


6

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


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


0

The first part of the solution was – as I said in the question – to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window. An alternative means for creating such an index, as suggested by @mpag, is via SQL: CREATE UNIQUE INDEX uidx_engagement ON Engagement (Emp_id, Year, ...


1

I think you need a LEFT JOIN or an inline subquery. The FROM b is also missing. For the NULL to be converted to 0, you need the equivalent of COALESCE() or ISNULL(), which is the Nz() function in Access: SELECT a.partNumber, Nz( (SELECT SUM(b.purchased) FROM b WHERE b.partID = a.partID ), 0) AS sum_purchased FROM ...


0

Thanks for the info! fred & WGLJ. The option of using SQL to make the change works - add the sql commands to a new query's sql view and run. There is no feedback of what happened, but when you open the table, you'll see.


1

Where My_ID = ID0001, ID0002, ID0003… IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<10,"ID000" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<100,"ID00" & Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1, IIF(Val(Right(DMax("[My_ID]","[MyTableName]"),4))+1<1000,"ID0" & Val(...



Top 50 recent answers are included