Recent content by JonFer

Assuming you want 5th percentile to be the higher costs/size and 95th to be the lower costs/size I think this should work: SELECT Min([CostsPerSize]) AS [5th percentile Operating Costs/Property] FROM (SELECT TOP 5 PERCENT [Operating Costs/Property Size] As CostsPerSize FROM [STEP 1...

Write 2 queries that summarize each table by AcctName and TradePrice and include a record count and a sum of the Amount. Write a 3rd query that joins the 1st two on AcctName and TradePrice and include both record counts and add a flag if the Amounts are equal. Run this as a Make Table and add...

Can you show the rows from DISP where DOCUMENT = '000039111' ? You will probably need to either: 1) add another field in the join or 2) join to a derived table (instead of DISP) which selects one row per DOCUMENT from DISP.

Can you provide the structure of the OTHER table which has the EMPLID, "WorkMonth" (or date?), and Hours? Is there one record per EmplID per month already or is there a date range for their time working? Or is it just a list of EmplIds and you're assuming they're full time for the months...

What is the structure of the table you'd be selecting from and excluding the leave months? Can you show a few rows of data?

Check that the combination of fields being used in the Join conditions for each table are indexed. Move the Where conditions that severely restrict the number of rows to be a Join condition and do them early. Determine which Inner Joins would most restrict the number of records and force...

See what this returns (using Partition By with Row_Number Over() ) when run with your history table. Select A.* From ( Select *, Row_Number() Over(Partition By TaskID Order By TaskID, TransDate Desc) as RowNum From TBL3 ) as A Where RowNum < 3

If you can create tables in your DB, then you could make a table with the from and to ages and maybe the age range description. Then join to your table with: Floor(Age) Between AgeFrom and AgeTo This helps keep your SQL simple, makes it easy to later change the age ranges, and allows you...

What are the primary keys for each table?

Charges have 1 or more Samples Charges have 1 or more Tests Test belongs to 1 Charge Sample belongs to 1 Charge Tests have 1 or more Samples but ONLY for the Charge associated with the Test Test table: Foreign Key ref to the Charge table Unique Key of TestID + ChargeID (to allow Foreign key...

For your criteria to find 1+ years old records, you could compare getdate() to a calculated date using the month/day from ADDTIME and using the year from "Year(getdate())-1". That will get you any number of prior years with one check. In your Select you can use a Case statement to generate the...

Start with a query that returns the rows you want to count - unique children per month per nursery: Select Distinct NurseShort, DateMonth, ChildID From YourTable Then use that query as the source for your crosstab query with NurseryShort as the RowHeading, DateMonth as the ColumnHeading...

You're right - I should have tested it since Delete rules in Access are so arbitrary. This works in SQL Server but not Access. Access only allowed it when you inner join (ID=ID) on the primary/unique keys (which wouldn't help here) and even then it did not allow a self-join. Hopefully later...

Corrected syntax: DELETE T1.* FROM MyTable T1 Inner Join MyTable T2 On T2.C10 = T1.C1 AND T2.C9 = T1.C2 AND T2.C8 = T1.C3 AND T2.C7 = T1.C4 AND T2.C6 = T1.C5 AND T2.C5 = T1.C6 AND T2.C4 = T1.C7 AND T2.C3 = T1.C8 AND T2.C2 = T1.C9 AND T2.C1 = T1.C10 Where...

A variation of ByteMyzer's solution - if your ID field is a primary key to the table then Access should allow you to do this: DELETE T1.* FROM MyTable T1 Inner Join MyTable T2 WHERE T2.ID < T1.ID AND T2.C10 = T1.C1 AND T2.C9 = T1.C2 AND T2.C8 = T1.C3 AND T2.C7 = T1.C4 AND...