Search results for query: *

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

I think if you're starting with generating all permutations and then filtering out the duplicates, it will be difficult to improve. You want to think of a way to reduce the initial number of records generated. Maybe something like generating combinations of only the unique values first and...

Translate the Fed Ex data to this table format (FedExZones): DestFrom DestTo Zone 000 004 NA 005 005 6 010 011 6 012 012 5 013 051 6 052 052 5 053 053 6 054 054 5 055 055 6 056 056 5 057...

You could split the FedEx data into DestFrom and DestTo zip fields and then use the Between criteria to find the zone. If a single destination value is listed, just copy it to the DestTo field as well. There should be an easily manageable number of rows.

If you only have 1 number that is repeated, you can add a column to the test table called DupSeq (duplicate sequence), and set it to indicate the sequence order of the repeated numbers like this: K KVal DupSeq 1 10 2 25 A 3 25 B 4 25 C 5 37 Then you...

You can read this post where the logic for combinations and permutations was explained with examples. You can modify your criteria to avoid the duplicates in the inner select. http://www.tek-tips.com/viewthread.cfm?qid=1673713