Select Query Based On Another Field Value

Jun 28, 2007

Hi,

I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.

I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).

I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c)
and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.

Can anyone advise?

Thanks,

Ryan

View Replies

Select A Tab Based On Field Value

Sep 25, 2006

Is it possible to have a specific tab in a tab control selected automatically based on the value of a combo box? Not by what is selected, but by what is already stored in the field.

For example, if have a combo box with a value "shirts," the "shirt types" tab is automatically displayed in the tab control box.

View 1 Replies View Related

How To Select Records Based On Summation Of Another Field

Jan 14, 2007

Hello,

I have a dataset that consists of Stands within a forest inventory. There are two Fields: the first field is StandValue (dollars), the other field is the StandArea( hectares).

I want to select the highest valued stands BUT the sum of the individual areas found in "standArea" cant exceed 10,000 hectares. Can anyone help me figure out an SQL query for this, or any other suggestions on how to proceed would be greatly appreciated, thanks
Greg

View 2 Replies View Related

Select 10 Or Fewer Records Based On ID Field Within Table.

Apr 19, 2007

Hello,

I've looked through the thread and was unable to find exactly what I'm looking for ?

Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.

What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.

So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.

Any help would be greatly appreciated.

Thank you

View 6 Replies View Related

Modules & VBA :: Adding Various Values Based On Select Case To Value Of Field

Oct 1, 2014

I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.

Code:
Private Sub ProductID_AfterUpdate()
Dim qflPrice As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlQry As String
Dim instID As Integer

[Code] .....

View 5 Replies View Related

Select Query Based On A User Choice

Nov 13, 2006

Hi,

I would like to run a simple select query, where the CRITERIA is based on the user choice.
I have a form with a combo box, with a few choices, and a button that will trigger a macro with one query for now. I can store the choice in to a variable e.g. "town", using Microsoft VB code.
How can I transfer the variable to the CRITERIA field in the query, so whenever I chose a specific "town", my query will select the records for that specific town.
I want to use one query, and I don't want to have to "hard type the criteria in to the cells' query", but I would like to use a variable that can change and will be read it from the user choice through combo box.

If I am not clear enough, please let me know.

Thank you!

View 2 Replies View Related

Query For Populating A Form Based On Different Select Criteria

Feb 20, 2006

Hi all,
I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:

UniqueID
ChartNumber
PatientLastName and PatientFirstName (two fields)

since they may not have the UniqueID readily available to them.

Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...

thanks!
vrpres

View 1 Replies View Related

Select Equal Type - Based On Random Query

Oct 6, 2006

Good Afternoon Everybody,

Apologies for imposing on you all but I was wondering whether or not you could help me out??

Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field �SectionHeadings� such that out of the 30 �random� questions that it currently generates it will provides me on completion with the following:

5 From Set1
5 From Set2
5 From Set 3 �..etc etc

It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.

All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I�ve already done and show me what needs to be done in order to achieve my aim.

I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.

Best Regards

CarolW

View 5 Replies View Related

Running A Query Based On A Select Record In The Form

Jun 13, 2006

Hi,

I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.

Got any examples of code I could have a play with

I am struggling here so need any help I can get!!

Cheers
Paul

View 1 Replies View Related

Queries :: Select Query To Pull Records Based On Multiple Strings Entered By A User?

May 1, 2013

Is it possible to run a basic select query to pull records based on multiple strings entered by a user?

I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'

This allows the user to input one MMDBID and the records are retrieved from the db.

I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.

But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above

SQL statement (Like) is below :

SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));

View 10 Replies View Related

IF THEN ELSE In SELECT QUERY On One Field:

May 27, 2005

PLEASE HELP!

Hello:

I have an issue I've run into and have searched around and tried to resolve. The issue is that I have a table that is updated once a week with new data, calculations are performed on new fields, etc...Ok, no problem on that. Oh, to give you a quick overview, the table is for Open and Received orders. Ok, the new field is Actual Received Date. Well, if open, obviously it's not received. If received, it's placed in that field, if Open, that field is blank for that record. I need to create a SELECT QUERY, would save TONS of time here if possible, to do an IF THEN ELSE type aspect that basically, if Actual Receive Date is present, then use this calc, if not, use that calc, etc...ANY IDEAS? Take a look below for additional information. The following are ONLY EXAMPLES to give you a better understanding of the issue.

1 Table, 1 Query, 2 Fields, IF THEN ELSE EXPRESSION within SELECT QUERY under 1 Field.

Field name in query = "DAYS LATE"

ISSUE: (in vb view)

Dim Value1 as String
Dim Value2 as String

Value1 = Date() - [mDueDate]
Value2 = [mActReceiveDate] - [mDueDate]

If IsNull([Tables]![tblOrdersMain]![mActReceiveDate].value) = True Then
[Query]![Query]![DAYS LATE].value = Value1
Else
[Query]![Query]![DAYS LATE].value = Value2
End If

Well, definitely need HELP! Any questions or anything please let me know.

Thanks!

View 1 Replies View Related

Passing A Field Name Into A SELECT Query?

Aug 8, 2006

I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.

I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.

My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?

can anyone help on this, or have any other ideas?

Thanks
Andrew:

View 4 Replies View Related

Queries :: Field Header In Query Based On Other Field Data

Jun 28, 2013

I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.

The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".

Is this even possible?

View 4 Replies View Related

Queries :: Query To Select All Data Regarding A Clicked Field

Mar 1, 2015

I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.

I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.

I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.

View 14 Replies View Related

New Query Field Based On Existing Field Logic

Oct 8, 2014

I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.

View 3 Replies View Related

Queries :: Multi-Select List Box As Filter For Query Field

May 16, 2013

On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.

View 12 Replies View Related

Queries :: Form To Select Parameter - Causing New Field In Query Design

Jun 16, 2015

I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.

Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.

View 1 Replies View Related

Queries :: Update Table With Values From Field Using Results From Select Query

Sep 23, 2014

Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.

How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?

View 1 Replies View Related

Query Based On Format Of A Field

Feb 6, 2006

All,

I have a Postcode field in my table and I want to be able to check the data to make sure that it is a correct UK postcode.

Is there away that I can find out the format of the data, to be able to run a query against it, or is there a better way of doing it?

I need to account for all types of UK postcodes (A1 2BC, A12 3BC, AB12 3CD, WC2A 3BC). There are also foreign postcodes in this field.

Help appreciated!

View 2 Replies View Related

Default Value In A Field Based On A Query

May 31, 2006

Hi,

I have a query which returns 1 value (The description associated with the maximum ID in the table).

I have a Text box on a form and I would like the default value of the text box to be the the value from the above query.

I have tried to build an expression using the expression builder by selecting the query and the field that I am interested in, in that query but I can't seem to get it working.

Could anyone please advise how I can assign a result from a query to the default value of a field on a form.

Many Thanks

View 1 Replies View Related

Query Based On Attachment Field

Jan 24, 2015

I would like to know what criteria is used to return records based on a field with data type attachment.

I am using "IsNull" and "IsEmpty" but neither of these work.

View 8 Replies View Related

Select Based On Count

Sep 18, 2006

I am feeling really silly right now. This should be simple but .....

I have a problem i just simply can not get my head around. Hope some one can help.

There are two tables in this db.

There is a one to many relationship set up between them

I want a query that will show records in the parent table only when there are a specific number of related records in the child table.

Does anyone know if this can be done and if so how?

View 10 Replies View Related

How To Set Query Criteria Based On # Of Characters In A Field

Sep 15, 2005

Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)

Russ

View 2 Replies View Related

Update Query Based On Length Of Value In Field

Jul 12, 2007

I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?

View 1 Replies View Related

Unique Query Based On Single Field?

Apr 11, 2008

Hi Guys, I'm new here and fairly new to anything indepth with Access.

I'm using Access 2003 and have a table which has a number of customer records. The two relevant fields are the CustomerID which is a simple integer and OrderDate obviously this is a date field!

What I need (to link in with the other forms) is a query that will return one record for each CustomerID it should be the most recent OrderDate.

I've had a few attempts and done a couple of searches but can't seem to find exactly what I'm looking for. If someone could point me in the right direction I'm happy to play with sample code and read up on bits other people have done (I tend to learn better that way) rather than just being given some code!

Thanks for any help you could can provide.

View 1 Replies View Related

Queries :: Query To Total Based On Value Of A Field?

Jul 31, 2013

the relevant fields look something like this:

TellerNum - Date - Field1 - Field2 - Field2

I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:

TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..

View 3 Replies View Related