Find First Record In A Set Of Fields

Nov 8, 2005

I have 12 fields, each containing a number. I want to display the last non zero value in a query. I've looked at "last" but this returns the items in chronological order, which is not neccessarily the order my data is entered in.

eg.
fields: 1 2 3 4 5 6 7 8 9 10
data: 0 7 3 0 0 5 4 0 0 0

I would want the query to return 4, even if the last value entered was the 7.

To make it more complicated, I have many rows of data for each set I am using, and have to aggregate these based on a key column

eg.

Fields: key 1 2 3 4 5 6
Data: 1 0 0 0 2 0 1
1 0 0 0 3 0 3
1 6 5 4 3 2 1
2 1 2 3 4 5 6
2 0 0 0 0 0 0

Any tips?

Cheers
Kev

View Replies


ADVERTISEMENT

General :: Find Record From Field In Subform And Then Return Its Parent Record

Feb 6, 2014

I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?

Can this be done? because if i use find it will only search the filtered form i have onload of the form?

My onload event is based on fosusername()

View 3 Replies View Related

Find Unset Date Fields

Jan 17, 2006

I've got an invoices table in my database, and I've got a payment approved checkbox that is false by default and a decision date field.

I want to find all outstanding invoices, and thought the best way to do this would be to check if the decision date is null, but that doesn't work.

How do I check for a blank field?

View 2 Replies View Related

Queries :: Find If Value Exist In Either Of 2 Fields

Aug 7, 2015

I need to find all MaintItems which field MIC don't exist in either MIC1 or MIC2. So the only result which I need to see is MaintItem '5100161086' as MIC <> MIC1 Unfortunately I can create query which would approve '5100662734' as well

MaintItem ------MIC------ MIC1 ------ MIC2
5100634036 50000173 50000173
5100662790 50000174 50000174
5100662734 50000145 50000145 50000180
5100662734 50000180 50000145 50000180
5100161086 500001711 50000173

So bottom line I need a query which will display only the row below as MIC doesn't exist in neither MIC1 nore MIC2. Also I will have to deal with much more MICs soon so maybe there is a way to concatenate them and compare, isn't it? 5100161086 500001711 50000173

Attached query is what I got so far, but that displays last 3 rows of table above

View 3 Replies View Related

Queries :: Find Max Value From 3 Fields In 3 Tables

Nov 23, 2014

I am trying to create a query and in the expression builder to find the max value from 3 different fields in 3 other queries.So each of the fields are called "TopSpeed" and the 3 queries are called "Test", "Training" & "Race".So in my new query I would like to return the MAX speed value from the 3 combined "TopSpeed" fields.Something like

MAX(DMAX([TopSpeed], [Test]), DMAX([TopSpeed], [Training]), DMAX([TopSpeed], [Race]))

View 1 Replies View Related

How To Find Sum Of Multiple Fields In A Table

Nov 10, 2014

i need to find sum of three or more fields in a table.

View 6 Replies View Related

Forms :: Other Fields To Find Data Automatically

Nov 13, 2014

In a form I have Two fields (which are of interest):

1) EmployeesName which is a lookup from another table named

TBLEmployees that contains two Fields
EmployeesName
EmployeesNumber

2) Second Field named EmployeesNumber which currently also using a lookup from table named TBLEmployees

if possible what I want to try and do when I select EmplyeeName from the dropdown list of names I want the EmplyeeNumber to be inserted into this field automatically rather than searching through the dropdown list again is this possible?

View 7 Replies View Related

Queries :: Find A Field Value From Similar Fields

Oct 11, 2013

I need to do a query to find a field value from similar fields. Table 1 has fields(customer id,...) and table 2 has fields (customer id, address,...). I need to use customer id from table 1 to find address in table 2. Both customer id fields in both tables is the same.

View 4 Replies View Related

Find Records Where 1 Field Matches But Other Fields Vary

Feb 14, 2007

I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.

While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?

View 2 Replies View Related

Select Query To Find Empty Date Fields

Jan 26, 2008

I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:

View 2 Replies View Related

Queries :: Find Difference Between Multiple Fields In Two Tables

May 8, 2013

I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].

View 2 Replies View Related

Modules & VBA :: Find A Way To Merge Tables And Combine Fields That Have Same ID

Feb 27, 2014

We are a non-profit that does blind mailings for our membership drive.
The company who we buy names and addresses from sends us a delimited file that has these fields as the headings

" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"

Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles

"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.

I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.

"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"

View 4 Replies View Related

Queries :: Find Duplicate Records In Table With Two Fields

Aug 29, 2013

I want query to find duplicate records, i have two field in one table

Cusip and category

cusip and category are many or duplicates

but in one cusip category should be the same if not then provide the cusip which has different category used

like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A

result should be

CusipCategory
123R
123R
123A

View 6 Replies View Related

General :: Find Specific Row And Copy Its Fields To Array

Feb 16, 2014

I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.

What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.

So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.

View 5 Replies View Related

How To Use Key Find A Record ?

Aug 17, 2005

Hello to all,
Little new in Access i would like to know how to use the key to find directly to a record in a table.
ie : i have a table where the primary key (unique) is a date, using VBA i would like to know the instruction to find directly the record 01/06/2004 for exemple.
Thanks in advance.
VINCENT

View 2 Replies View Related

Find Record

Jun 14, 2005

I forgot to mention that my form is based upon a table, i have created other tables and forms based on this one therefore I don't want to change a lot of stuff. I tried the forum but nothing helped.



I have a form with about 10 fields, i added a find button to search for a record, using three fields, when this button is click i want it to display all the info pertaining to the search criteria, (this will be displayed on the same form populating in the form which contains other feilds) or a error saying record not found.

Thank You

View 2 Replies View Related

Find Record

Mar 30, 2006

Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.

thanks

View 3 Replies View Related

Find Record

Mar 30, 2006

Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.

thanks

View 7 Replies View Related

Find Record

Sep 26, 2006

I have navigation between forms and when a user navigations from the main form(candidate) to another form(1) and then back there is only 1 record displayed on the candidate form as it is only bringing back the record tied in by the application number.
So if you then want to search for another candidate it doesn't work because the candidate form is filtered.

I've tried using this in my find function but it doesn't work: DoCmd.GoToRecord , , acFirst

Can anyone help?
Thanks,
Dan

View 2 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

General :: Find Average With Blank Fields In Access 2010

Nov 29, 2012

I am trying to find an average of four fields in either a form or query. Basically I have figures for [Grade 1], [Grade 2] but [Grade 3] is an empty field. I need to include all three field because there are sometimes 3 grades, but a majority are 2 grades. How to do so?

View 2 Replies View Related

Find Matching Dates In Tables A And B / Then Populate Blank Fields In A From B

Apr 30, 2012

I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.

View 3 Replies View Related

Using Query To Find A Record

Jun 29, 2005

The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.


Private Sub Command7_Click()


'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()


strSql = "Select adminpass From admin"


If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then

MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"

ElseIf (txtpassword = "admin" And combusername = strSql) Then

DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"


ElseIf (txtpassword = "user" And combusername = "user") Then

DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"

Else

MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus


End If

End Sub

View 3 Replies View Related

Find Record Error

Feb 10, 2005

I have a form with a form header section that contains a combo box and a text box. The form also has a detail section that has a tab control that contains the rest of the data entry boxes. I put a Find Record button in the detail section of my form. When I try to Find Record using the combobox or textbox values in the form header, I get an error saying it cant search the tab control, use the Gotocontrol action before the Find Record Action, change current field action from yes to no, or change the focus to a searchable control. If I search items in the detail section I do not get errors. The code created by the Find Record button is below. I'm not sure how to get it to set focus to the appropriate box in the form header section if that is the field I am searching on.

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFind_Click:
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

View 1 Replies View Related

Find Record Problem

May 31, 2005

Hello,

I have a large database I set up and we have been using fine for a couple of years now.

All of the sudden, anytime Find Record is clicked and the value is entered, I am getting the following error, on ALL forms. Nothing has been changed in the DB itself and it worked GREAT before.

The control 'Command 138' the macro is attempting to search can't be searched.

Try one of the following:
* Add a GoToControl before the FindRecord action.
* For the FindRecord action, change the Only Current Field action argument from yes to no.
* Change the focus to a searchable control.

Can anyone help? It is VERY frusterating and I can't figure it out.

Thanks so much! ;)

View 5 Replies View Related

Find Record Based On Cbo Box

Jun 6, 2005

I have a combo box that displays LastName FirstName.

If I have two people with the same lastname it finds the first one in the table w/ that last name. Please help with my code so it searches by firstname and last name. Thanks in advance.

Kacy

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Contact_LastName] = '" & Me![cboContact] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

View 4 Replies View Related







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