A Ranking issue

[kit_sune]

I have a ranking issue. I put together the below expression but I get "0" for every value - One of them should read "1".

I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.

So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.

I don't want to create multiple queries to do this, is there any way to do this?

Here's what I have:

DDR: (Select count(*) from tblMyDataImport  Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )

Any ideas on what I need to change? Thank you,

~Kit

 

[CJ_London]

try

DDR: (Select count(*) from tblMyDataImport [COLOR=red]AS TMP[/COLOR] Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR])

 

[MarkK]

You need to be able to compare values in the table tblMyDataImport to other values, maybe from another table, maybe from code, maybe from user input, we don't know.

Mr. London is suggesting you alias your tables, but that assumes that your query has more than one table in it.

Maybe you can say more about how this query is executed, and where the TIMESTAMP value will come from that you want to ensure is > than the one that exists in the table. Then we can determine how best to get that information into your query.

 

[kit_sune]

CJ_London's recommendation actually worked just fine - I'm assuming this method creates a temporary copy of the table to compare to?

Maybe you can say more about how this query is executed, and where the TIMESTAMP value will come from that you want to ensure is > than the one that exists in the table. Then we can determine how best to get that information into your query.

Sorry for the long delay, got caught up in work but now I need to return to this.

Here we go: "tblDataImport" stores data imported from of a delimited text file. It's a sort of "Hold" table that allows me to pull the data in for manipulation. My report sometimes shows multiple entries for a single job. I.E. I have five jobs, but technitian had to work a little bit one day, and come back the following day to finish one of them resulting in two entries to document their work. Unfortunately this IS does not accurately differentiate between two actions, except by using the timestamp and date that they were signed off, thus making it tough to count the actual number of "Jobs" without visually inspecting and I'd rather not do that.

The timestamp field is is an 8 digit hhmmssss format (14353612 - Meaning 2:35:36pm) and so it's pretty easy to figure out which one was signed off first.

My plan is to use a query to pull all the data from the hold table, evaluate the Job Numbers, dates, and times and determine which jobs are the first signed off, and so on. I planned on doing this by adding a column beside the job number where it would assign the values "000", "001", "002" and so to identify each job's actions in order. Then we would append these records to another table to use in reports and such.


Doing it CJ_London's method only gives me 0, 1, 2 etc, but it's easy enough to move on from here.

Thanks all,

~Kit

 

[CJ_London]

I'm assuming this method creates a temporary copy of the table to compare to

Mr. London is suggesting you alias your tables

Not quite - it is a subquery which produces a subset of the data based on the criteria. You need to alias in order to compare with the 'original' table. Not ealso this is only bringing through as single value - count(*). If it was an aliased table you would have something like

SELECT * FROM Table1 INNER JOIN Table1 AS T2 WHERE Table1.SomeField=T2.SomeField

With regards assigning values it will be easier to use numeric values since the sort order for text is different (you can always display as format(order,"000"))

 

[kit_sune]

With regards assigning values it will be easier to use numeric values since the sort order for text is different (you can always display as format(order,"000"))

That worked great, it really beats using an IIF statement twice.

DDR: IIf(Len((Select count(*) from tblG081MDCDataImport AS TMP Where [tblG081MDCDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblG081MDCDataImport].[EVENTID]=[EVENTID] AND [tblG081MDCDataImport].[ACFT]=[ACFT]))=1,"00",IIf(Len((Select count(*) from tblG081MDCDataImport AS TMP Where [tblG081MDCDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblG081MDCDataImport].[EVENTID]=[EVENTID] AND [tblG081MDCDataImport].[ACFT]=[ACFT]))=2,"0","")) & (Select count(*) from tblG081MDCDataImport AS TMP Where [tblG081MDCDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblG081MDCDataImport].[EVENTID]=[EVENTID] AND [tblG081MDCDataImport].[ACFT]=[ACFT])
 
Turned into-
DDR: format((Select count(*) from tblG081MDCDataImport AS TMP Where [tblG081MDCDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblG081MDCDataImport].[EVENTID]=[EVENTID] AND [tblG081MDCDataImport].[ACFT]=[ACFT]),"000")