Quick Queries List

Select

SELECT [field1], [field3], * 
FROM [table] 
WHERE [field1] = {value 1} 
 AND [field2] = {"value 2"}

Delete

DELETE FROM [table] 
WHERE [id field] = {value 1} 
 OR [id field] = {value 2}

Update

UPDATE [table] 
SET [field 1] = {"value 1"}, 
 [field 2] = {value 2} 
WHERE [match field] = {match value}

Find Duplicates

SELECT [field], Sum(1) As [Copies] 
FROM [table] 
GROUP BY [field] 
HAVING Sum(1) > 1 
ORDER BY Sum(1) DESC

Duplicate Addresses

SELECT [Address1], [Postcode], Sum(1) As [Copies] 
FROM [Patients] 
GROUP BY [Address1], [Postcode] 
HAVING Sum(1) > 1 
ORDER BY Sum(1) DESC

Count

SELECT [field], Sum(1) As [Copies] 
FROM [table] 
GROUP BY [field] 
ORDER BY Sum(1) DESC

Copy a table into a passworded database

INSERT INTO [;DATABASE=\\serverName\sharedFolder\subFolder(s)\databaseName.mdb;PWD=databasepassword].[destinationTable]
SELECT * FROM [sourceTable]

Create Table

CREATE TABLE [table] 
( 
	[ID] INTEGER CONSTRAINT [MyUniqueKey] PRIMARY KEY, 
	[remoteID] {INTEGER}, 
	[textField1] {TEXT}, 
	[textField2] {TEXT} ({23}), 
	[dateTime1] {DATETIME}, 
	CONSTRAINT [ExtraUniqueKey1] UNIQUE ([remoteID], [textField1]) 
)

Remove Table

DROP TABLE [table]

Add Field

ALTER TABLE [table] 
ADD COLUMN [field] {TEXT} ({50})

Remove Field

ALTER TABLE [table] 
DROP COLUMN [field]

Recall Reprint Problems

SELECT * 
FROM [patients recall history] AS h 
 LEFT JOIN [patients recall] AS r 
 ON r.[recall type]=h.[recall type] AND r.[patient key]=h.[patient key] 
WHERE r.[Last Letter Band] = {0}

Optomnotes Archiving

SELECT 'scan archive flag not set' AS Problem, t.thumbnailid AS ThumbnailID, 
 t.archivenumber AS ThumbArchiveNumber, s.isarchived AS ScanArchived, 
 s.archivevolumelabel AS ScanArchiveNumber, t.deleted AS ThumbDeleted, 
 (s.iscurrent=False) AS ScanDeleted, IIF(IsNull(i.imageid),'No','Yes') AS ImageInDB, 
 "UPDATE tblscannedimages SET isarchived=true WHERE thumbnailid=" & t.thumbnailid AS ProbableFix 
FROM (tblscannedimages AS s 
 LEFT JOIN tblthumbnails AS t ON t.thumbnailid=s.thumbnailid) 
 LEFT JOIN tblimages AS i ON i.imageid=t.imageid 
WHERE s.isarchived=0 AND IsNull(i.imageid) 
 
UNION ALL 
SELECT 'scan and thumbnail mismatch' AS Problem, t.thumbnailid AS ThumbnailID, 
 t.archivenumber AS ThumbArchiveNumber, s.isarchived AS ScanArchived, 
 s.archivevolumelabel AS ScanArchiveNumber, t.deleted AS ThumbDeleted, 
 (s.iscurrent=False) AS ScanDeleted, IIF(IsNull(i.imageid),'No','Yes') AS ImageInDB, 
 IIF(IsNull(i.imageid), IIF(s.isarchived, 
   "Find the archive it's in and UPDATE tblthumbnails SET archivenumber={(!!thatArchiveNumber!!)} WHERE thumbnailid=" & t.thumbnailid, 
   "UPDATE tblscannedimages SET isarchived=true WHERE thumbnailid=" & t.thumbnailid), 
  IIF(s.isarchived, 
   "UPDATE tblscannedimages SET isarchived=false WHERE thumbnailid=" & t.thumbnailid, 
   "UPDATE tblthumbnails SET archivenumber=0 WHERE thumbnailid=" & t.thumbnailid)) AS ProbableFix 
FROM (tblthumbnails AS t 
 LEFT JOIN tblimages AS i ON i.imageid=t.imageid) 
 LEFT JOIN tblscannedimages AS s ON t.thumbnailid=s.thumbnailid 
WHERE (t.archivenumber=0 AND (s.isarchived OR s.archivevolumelabel & ""<>"")) 
 OR (t.archivenumber<>0 AND (s.isarchived=False OR s.archivevolumelabel & ""="")) 
 OR t.deleted=s.iscurrent 
 
UNION ALL 
SELECT 'image expected' AS Problem, t.thumbnailid AS ThumbnailID, 
 t.archivenumber AS ThumbArchiveNumber, s.isarchived AS ScanArchived, 
 s.archivevolumelabel AS ScanArchiveNumber, t.deleted AS ThumbDeleted, 
 (s.iscurrent=False) AS ScanDeleted, IIF(IsNull(i.imageid),'No','Yes') AS ImageInDB, 
 IIF(t.archivenumber<>0, "UPDATE tblscannedimages SET isarchived=true WHERE thumbnailid=" & t.thumbnailid, 
  "Find the archive it's in and UPDATE tblthumbnails SET archivenumber={(!!thatArchiveNumber!!)} WHERE thumbnailid=" & t.thumbnailid) AS ProbableFix 
FROM (tblthumbnails AS t 
 LEFT JOIN tblimages AS i ON i.imageid=t.imageid) 
 LEFT JOIN tblscannedimages AS s ON t.thumbnailid=s.thumbnailid 
WHERE IsNull(i.imageid) AND (t.archivenumber=0 OR s.isarchived=False OR s.archivevolumelabel & ""<>"")