Query To Extract Greatest Number

Aug 14, 2007

I have a table that keeps track of how many times an account is listed. I want to write a query that will only return the greatest number of occurences for that account. For example if acct 12345 has 3 occurences in the database I only want to return the information that is associated with the #3 occurence. As stated there is a field that calculates the number of occurences for each account so in this case there would be a record for 1, 2, and 3 occurence so I only want to see #3. Hope that makes sense. Thanks!

View Replies


ADVERTISEMENT

Extract A Number From A String?

Sep 16, 2013

I do have a module to extract a number from a string :

Public Function ExtractedValue(strSent) As Double
If IsNull(strSent) Then
Exit Function
End If
Dim strTemp As String
Dim intLoop As Integer

[code]....

My problem is that the string I do have is like:

19.0 EA
195 PAC
42 M
150 L

the problem is that when the number is like 19.0 EA the value I get is 190, in reality it should be 19. The rest is fine as there are no dot's.

View 3 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related

How To Extract Date From Personal ID Number

Oct 4, 2006

Hello,

I would appreciate if someone could help on this problem.
What I want to do is to add a field in a form that would automatically show the date of person's birth after entering the persons ID. For example, I enter 39101012345. The first number (3) shows only the gender so I don't need it. The next 6 numbers show the date of persons birth - it is 910101 and it means that the birthdate is 1991.01.01
I need this field in a date format because later I will have to do some calculations concerned with the birthdate of that person.

Thanks in advance!

p.s. beer for help is quaranteed! :)

View 4 Replies View Related

General :: How To Extract Number From Memo Field

Jun 17, 2014

for example how can I extract the following number from the field ITEM 117145 5410076462162, 714774 117072 5410076462223 ,714768 117153 5410076462285,733289 998214 5410076462193 from this record. The field is memo. The common: the string "+" always is in front I tried this Expr2: InStr([ITEM],"+"), but it only give me first one.

The following is record
BARCODE Retail Line Code NSL Code Prod Ean Name Size Facings Capacity Pos Comments Offer Description Offer Type Offer No 5ELKAHQ*agiaji+ 714766 117145 5410076462162 NS PRNGLS SUR CRM AND ON 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiagh+ 714774 117072 5410076462223 PRNGLS ORIG 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiddd+ 714768 117153 5410076462285 PRNGLS SLT/VNGR 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agibia+ 733289 998214 5410076462193 PRNGLS BBQ 190G 3F 15 BUY ONE GET ONE FREE MV 105574

View 6 Replies View Related

Queries :: Extract Or Calculate Number Of Records In A Sequence

Jan 4, 2014

How to extract or calculate the number of records in a sequence - e.g. in a table/query ordered by ID and date, the number of consecutive records by date for a given ID that have a value >= 50 in a given field. Is there a way of doing this purely within a query, or would I have to resort to a VBA loop through a recordset and keep a count of the consecutive records that match that criteria?

View 3 Replies View Related

Greatest User

Sep 13, 2005

Situation:
When logging into my network I record several key information about every computer. The only two fields that concern me at the moment are Username and Computername. I am trying to retrieve the user of each machine. My problem is sometimes users will log into machines that are not theirs. This situation can make my queries somewhat inacurate as I will explain below.

Sample data:

Username Computername
JSmith Comp1
TJones Comp2
TJones Comp1
JSmith Comp1


Perfect World:

What I would like is to individualise this information to the specific party in one complete sql statement. So that I can run the statment with each machine as the variable and allow it to pull the appropriate information for the user.

Example:

JSmith uses comp1
Tjones uses comp2

What I have tried so far:

Max() this seems to treat them as alphabetic characters and returns the user with the highest alpha/numeric value that has logged onto the computer.

Min() see Max()

Last() this seems to be the most accurate option I have, because it should be correct 90% of the time. Though it still has its inherent flaws as the primary user does not have to be the last user necessarily.

I have tried to look for information in both my help file and this website, if someone would please point me in a direction I would greatly appreciate it. Thank you.

View 8 Replies View Related

Return Greatest Value

Oct 31, 2006

I have a table that has multiple Account Numbers and their Balances, I need to only pull the records that have the largest value.

Example: Acct# Balance
2 $1.00
2 $500.00
2 $300.00
3 $25.00
3 $400.00

I need my query to pull only these records Acct # Balance
2 $500.00
3 $400.00

Please help me figure out how to only pull those records

View 1 Replies View Related

Greatest Values Question

Sep 27, 2007

Hi everyone

This seems like a really simple problem, but I can't figure it out...

-I have a table that contains machine problems, and their duration times
-I made a query that filters the problems based on the date of occurance, and adds up the duration times for a specified problem for the date range

Question: How do I get it to display the top 5 (i.e. 5 with the longest duration) problems? Can the max function work for more than one record?
:confused:

View 2 Replies View Related

Modules & VBA :: Greatest Common Divisor

Sep 16, 2014

How to find Greatest common divisor in MS Access?Have a 2 columns in my form: 1) amount; 2) GCD

E.G

amount | GCD
1200
500
400
100

Should be after runing code

amount | GCD
1200 | 12
500 | 5
400 | 4
100 | 1

I was looking form some function but I found only for Ms Excel :/

View 11 Replies View Related

Modules & VBA :: Unable To Extract Day Number From Date Field And Write It To Day Field

Jan 10, 2014

I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.

I want to extract the day number from the Date field, and write it to the Day field.

I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".

So far, my code looks as follows:

Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()

[Code]...

I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.

View 12 Replies View Related

Using A Query To Extract Email Addresses

Aug 17, 2005

Hi there,

I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.

I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?

Thank you in advance for any help! :confused:

Dan

View 5 Replies View Related

Extract UK Postcode With Expression In Query

Jan 19, 2006

Hi

If i wanted to run a query from customer records in my database and wanted a list of specific postcodes that matched the criteria how would i do this?

For example each customer records has a postcode BD4 4KL, LS9 7YH ETC

I want to categorise each postcode set to see for example all the customers with postcodes begining with have ordered factsheets.

In the criteria part of the query how would i write it so it only extracts specific postcodes from the database from the ones i require

e.g "HU1, HU2, HU3 HU4" - checks database - returns all postcodes beginning with this.

Please help me!

Thanks

View 14 Replies View Related

Select Query To Extract All But The Largest Value?

Oct 1, 2006

Hi, newbie question.

I have an existing select Query, that has a field of dates. I want a new select query based on that source that shows all records except for the one with the largest value date. There will be no duplicate dates. I tried:

SELECT Q_Tasks_Accs.End AS NotLatestEnd
FROM Q_Tasks_Accs
WHERE (((Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))));


but when I run the query I get error message:

"Cannot have an aggregate function in WHERE clause(Q_Tasks_Accs.End)<(Max([Q_Tasks_Accs].[End]))"

I clicked on Help but did not understand what it said. So any help from here would be appreciated.

Thanks

View 2 Replies View Related

Queries :: Extract Data From Query

Sep 15, 2013

I am trying to extract data from a query. Part of code when form loads, my questions is bold:

'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"

[code]...

i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.

View 3 Replies View Related

Extract Data To Left Of Dash In Query

Jun 20, 2005

I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.

How can I do it in a query, there is no find function. If there is an equivelant what is it?

Sample data:

22DF-RED
33AF-0
44TG-Blue


I need only the data to the left of the Dash

Thanks

Ziggy

View 2 Replies View Related

Making A Query That Can Extract 2 Closest Column Values

Dec 20, 2006

Hello I have a query in MSACCESS that looks like this

N| Q | A | B | C | D | E | F |
X|..4.|..1.|..6.|..7.|..9.|...|...|

now my problem is that I want to have a query select the 2 closes values to Q from columns either A B C or D and put them into columns E and F

I am just so stuck here, if anyone can help or give me a steer in a right direction i would greatly appreicate it!

View 1 Replies View Related

Tables :: Query To Extract Mailing Addresses From A Table?

Nov 28, 2012

I am trying to construct a query to extract mailing addresses from a table. I have individuals entered into a table (a separate record for each person) but if they are married I want an address such as Mr and Mrs J. Doe so that only one address label is printed off so that only one letter is sent out. If one of them dies then the address should only go to the surviving party eg Mrs J.

View 4 Replies View Related

Queries :: Using Query To Extract Data Based On Numbers

Feb 18, 2014

I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).

Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.

I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.

View 2 Replies View Related

Queries :: Possible To Extract First Letter Of A Surname In Access Query?

Jun 11, 2015

is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.

Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir

What I want the query to return:

Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A

View 6 Replies View Related

Modules & VBA :: Loop Through Query Results To Extract Email Addresses

Jun 27, 2013

I am trying to loop through a query results to extract email addresses from a query result.

I have the following code

NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

[Code] .....

If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?

Someone on another post said that sql uses US date format so i tried converting to us date and still no results?

View 6 Replies View Related

Queries :: Extract And Replace With Additional Data Append Delete Query

Jul 1, 2015

I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record

EG

ID1,10,write a report,2400

I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:

ID2,5,write a report,1200
ID3,5,write a report,1200

My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.

View 1 Replies View Related

Query That Result Incremental Number, With No Auto-Number

Sep 10, 2006

Well, here's my database :

Name : String
Address : String

I want to add an automatically generated column, "no.", to show which number it's in. Like this :

No. Name Address
1. A Unknown
2. B Unknown2

The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.

Is it possible to generate query that have "no." column in it ?

View 1 Replies View Related

Modules & VBA :: Sequence Number In Query Use A Starting Number

Sep 20, 2014

creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5

---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------

View 2 Replies View Related

Extract Data

Dec 6, 2005

Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.

Example :

Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC

View 3 Replies View Related

Extract Text

Sep 13, 2006

Hi,

I am currently working on a report that i need to extract part of the data from the memoranda field. i have a problem to extract the details that i need.

Example as follow :

Field Name = Memo
Details in Field = ?********~SPGEO1398789159173897 *********?

what i have to do is to extract the data starting from "SPGEO1398789159173897". Also the length of the number can be varied.

i have tried to use the instr, Trim, LTrim but still couldn't get what i wanted.

Can someone here please give me a hand on this

Much appreciate for your help

Ronny

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved