Autofill Query Field Based Upon Other Fields Within The Same Query?
Hi,
Baisically I got this query which controls the selection of Software within a system. i.e. It contains the fields Vender_ID, Application_ID, ApplicationEdition_ID and ApplicationVersion_ID. These auto-filter based upon the users selection (e.g. Vender=Adobe so Application=Acrobat/Photoshop/etc...)
Right some from the user software/application selection i want it so that it will automatically assign a License_ID defined within a table tblLicense_Information.
Baisically im asking whether i can put this in the 'criteria' box:
"SELECT tblSoftwareOverview.License_ID FROM tblSoftwareOverview WHERE ((tblSoftwareOverview.Vender_ID=qryHardwareSoftwar e.Vender_ID) AND (tblSoftwareOverview.Application_ID=qryHardwareSof tware.Application_ID) AND
(tblSoftwareOverview.ApplicationEdition_ID=qryHard wareSoftware.ApplicationEdition_ID)
AND(tblSoftwareOverview.ApplicationVersion_ID=qryH ardwareSoftware.ApplicationVersion_ID));"
I know the sql is not exactly right but you get the idea of what i want to achieve! But im guessing you cant really have an sql statement within an sql statement so any other suggestions on how i can do this would be much appreciated as the INSERT INTO command didnt like inserting into a query and when inserting straight into the table, the query failed...
cheers
View Complete Forum Thread with Replies
Related Forum Messages:
Autofill Fields Based On Results From Another Field In The Table?
I have an Access database with several tables and a multitude of subforms which are displayed on a single master form. The subforms are used to facilitate data entry. In several of the tables there are fields which are related and I would like to have some of these fields updated based on the results entered in the related field (i.e. the answer for one field depends on the other). Field 1Field 2 [facing][DISC_CODE] un3 up1 dn2 Field 1 is a simply a description of the basic dataset. This field is already set-up on form as a combo box that allows the user to choose one of three options. Field 2 is a code number used by another piece of software to identify a particular symbol. It is a new field being added to the database. There are 200 codes that identify a wide range of symbols for different types of data and I don’t want to have to look them up when I, or my assistants, are doing data entry. My question is this; is there anyway to have the DISC_CODE value, Field 2, automatically entered in the table when the value for Field 1 is selected in the combo box on the form?
View Replies !
Blank Fields In Crosstab Query Based On Previous Query
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing.... First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them. When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact) The SQL was written by Access not by me. :) Here is an example of the Crosstab SQL (which is using a previous query): TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE; ----------- qryTest2 SQL: (Grouping by to remove dups) SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME; Thanks for you time! :)
View Replies !
Question About Autofill Fields From One Individual Field
I have a form with the following Fields: Customer Name, Address, City, State, Postal Code, and Phone Number. I also have a field called License Number. This question is that once the data is entered the first time, is there an easy way so that if an individual that has been entered into the database comes back you can enter there License Number only and the other fields will fill in automatically. If so How. I am assuming so kind of query but i need help. Please Help..thanks
View Replies !
Query Two Fields Based On One Criteria
Hi all, I am endeavouring to filter a form based on the position of an individual. I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed. I have an underlying query that is populated by a combo box on an unbound form. This has worked when generating reports for individuals but I cannot get the required result when using the form. Thanking anyone in advance who can assist me with this. Regards, Rod
View Replies !
Query To Set Status Based Upon 2 Fields
I'm trying to create a report eventually, however, I need to get my query set up. My report should look like the following when complete. Timeframe (mth,qtr,year) # of Total Projects Completed - regardless of status # and % of Projects Completed within Requested Delivery Date # and % of Projects Completed within Committed Delivery Date # and % of Projects Completed within Requested and Committed Delivery Date I have 3 different fields to base my calculation from: Actual Delivery Date Requested Delivery Date Committed Delivery Date I have created 2 new fields that calculate the number of days: [Actual Delivery Date] - [Requested Delivery Date] [Actual Delivery Date] - [Committed Delivery Date] I'm trying to set up a "Status" field to indicate whether the project was completed "Within Requested", "Within Committed", or "Within Requested and Committed", however, I'm stumped. I've tried an Iff statement to no resolve. I don't want 10 queries just to get me there either. Please help! :confused: Thanks!
View Replies !
Query Based On Criteria In Two Fields?
Hi All I want to create a query which is based on two fields. One field is a date query i.e >=Date()-7 OR >=Date+7 The Other is a Combo box where the criteria is No My problem is that access is currently running this as two individual queries. Is it possible to make this query return data only when both fields are matching? Or do I have to run this as a parameter query? Thanks!!
View Replies !
Change Query Based On Form Fields
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input. I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query. However, if one of the fields is blank, the query doesn't return anything. Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria. It's not an error-checking issue, either, as entering info into all of the fields isn't required. For reference, the searchable fields are: Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name. A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum. There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one). I hope I'm clear in what I'm trying to accomplish :o Thanks in advance!
View Replies !
Query Based On Counting Text Fields
I have a query that checks whether a item was a pass or fail when it was tested, what i need is to count through the records and see what testing would be required for the next item of that type. All new items are tested 10 times, after that it can be a 'skipped item' for 9 more, then back on to test as a a '10th lot test' If a item fails testing, the count has to be reset so that 10 items are tested before it is able to be a 'skipped item' If an item is a 'skipped item' i need to know how many more items can be skipped before a full test is needed. I am using a query to give me results by item code, this lists all items with the item code the date and if it passed, skipped, or failed. I think that an total query should be able to do what i need, or i could just be barking up the wrong tree?!? the sql for the query is below.... SELECT tbl_max_ship_supp.[MAX Code], Count(tbl_lot_details.[IQA Passed]) AS [CountOfIQA Passed], tbl_lot_details.[IQA Passed], tbl_lot_details.Date FROM tbl_max_ship_supp INNER JOIN tbl_lot_details ON tbl_max_ship_supp.[MAX Code] = tbl_lot_details.[MAX Code] WHERE (((tbl_lot_details.[IQA Passed])="pass" Or (tbl_lot_details.[IQA Passed])="fail")) GROUP BY tbl_max_ship_supp.[MAX Code], tbl_lot_details.[IQA Passed], tbl_lot_details.Date HAVING (((tbl_max_ship_supp.[MAX Code])=[Enter card part number]) AND ((tbl_lot_details.[IQA Passed])="pass" Or (tbl_lot_details.[IQA Passed])="fail")) ORDER BY tbl_lot_details.Date DESC;
View Replies !
Query Based On Format Of A Field
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 Replies !
Select Query Based On Another Field Value
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 !
Default Value In A Field Based On A Query
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 Replies !
Update Query Based On Length Of Value In Field
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 Replies !
Unique Query Based On Single Field?
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 Replies !
Autofill Query?
Hi everyone, I have a table which includes several columns. Two of these columns are important for this update query I wish to do. In my table, I have two columns like this: API# Current Operator --------------------------------- 1 | 1 | 2 | Bob 3 | Joe 1 | Mike 3 | 2 | I am wondering if there is a way to run an update query which would automatically fill in the blank fields in column 2. The numbers in column 1 will always correspond to the same person in column 2. Thanks, Chris
View Replies !
Generate Value For Text Field In Report Based On Query
Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report. Basically if the field HOTOtal shows any vaue between: 16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below) I thought I could do a case statement on report open but I am getting an error that my expression contains no value. Here is the case statement Private Sub Report_Open(Cancel As Integer) Select Case Me.HOTotal Case 16 To 30 Me.HOType.Value = "Definitely evening type" Case 31 To 41 Me.HOType.Value = "Moderately evening type" Case 42 To 58 Me.HOType.Value = "Neither type" Case 59 To 69 Me.HOType.Value = "Moderately morning type" Case Else Me.HOType.Value = "Definitely morning type" End Select End Sub What else can I do?
View Replies !
Need: To AutoFill Value From Table/Query
I'm hoping someone here can help me out with existing code or at least point me in the right direction... In one of the databases I am developing, I need to set up the ability for one field in NEW records to be autofilled with the next value from a table or query. Details My database includes the following objects: tblSupervisors - supervisor information (including an indicator if they are 'active') tblCases - case information including name of supervisor assigned to review this case frmNewCases - allows data entry of new information to tblCases If someone enters a new case, the Supervisor field would (in an ideal world) automatically fill in with the name of the next active supervisor from tblSupervisors. Example The active supervisors in tblSupervisors are Joan, Mark, Lisa, and Gary. Case #1 is entered and the Supervisor field is automatically pre-filled as Joan. When Case #2 is entered later, the Supervisor field shows Mark. When Case #3 is entered later, the Supervisor field shows Lisa. However, because the person entering the case knows Lisa is on vacation, s/he can click a button and Lisa's name is replaced with Gary. Finally (for this example), Case #4 is entered and Joan is assigned as Supervisor again. Whew! Okay. That's it. (I hope.) Any and all help would be GREATLY appreciated!
View Replies !
How To Construct Dynamically A Table Field Based On Query Criteria.
Hello all, I have a temporary table with Yes/No fields (F101,F102,F103,....etc). In another table i have a field called ProductCode with values (101,102,103,....etc) Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes) Thank you in advance.
View Replies !
Question On Query To Flag A Field Based On Calculated Data
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View Replies !
Query Expression To Autofill Given Other Criteria
I have a grades_table with data regarding a grade a person gets. It contains like Name, Date, Grade, Grader_Name, Month, Year... I have another table (grader_table) that contains the Grader_Name, and how many Grades that grader needs done in a given month (i'm using two numbers to indicate month and year). What I would like to do is I have a form where all info for the grades_table is entered. When the query is run, I would like to have the month and Year field use the date given to auto fill numbers. So I picture it as like 6/5/07 is given, so the query records the date, but also stores the 5 and 7 in other columns. I tried this in my query for i think its title - Month: Month([Date]) but it says there one too many ")". I have a feeling I may also need help with the actual retrieval of a months score since it will cross tables, but this is the big question for now. Any help is appreciated.
View Replies !
Query Field To Concatenate Fields With Nil Values
I have an Access 2000 project and am trying in a query view to join 3 values into one. 2 of the values come from tables, the third comes from another query view (all linked in the query design screen) Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty. All values are strings. I have tried this formula in the query design grid: dbo.Value1 + '/' + dbo.Table2.Value2 + dbo.vieTable3.Value3 ..in an attempt to give the result Value1/Value2 or value1/Value3 according to the particular record. Unfortunately it doesn't work! ...just returns blank results when the query view is run. Any suggestions would be greatly appreciated thanks....nick
View Replies !
Autofill Based On Selection - Please Help
I am learning Access fairly quick, but still a newbie and stuck on an autofill problem. I have read everything I could find and it seems there may be code involved, which I'm not sure about. The problem seems simple enough: I want to populate field "Rate" in the table "Services" based on the selection from a dropdown menu referencing ClientID in the Clients table. Specifically, my form uses a drop down menu to select ClientID for the Client table to associate with fields in the Project table and I have imbedded another form at the bottom which contains fields from the Services table including the Rate field I want autofilled based on the ClientID selection. Here are the tables and fields I'm working with... ...table... Client ...fields... ClientID (PK) Rate <and more> ...table... Project ...fields... ProjectID (PK) ClientID <and more> ...table... Services ...fields... ServicesID (PK) ProjectID Rate <and more> Please be easy on me if this is a simple problem. Thanks!!!
View Replies !
Adding Fields In A Table To Create New Field In A Query
I've got a fields called rev code that contain the following values: field name: 110 131 250 255 258 field value: 7.49 6 11.25 12.11 78 I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101. How do I do that?
View Replies !
How Do I Create A Query To Move 2 Fields On The Same Row To Be 2 Rows In One Field
Hello, I have a big table like this: ID Name Race1 Race2 Race3 -- ---- ----- ----- ----- 1 a Asian russian How do I create a query to be like this (either in query or report)? I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value... ID Name Race -- --- ---- 1 a Asian Russian In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how? Thanks so much! Jenny.
View Replies !
Field Value Based On Other Fields
Here is what I am trying to do. I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.) How would I code for the value in the carrier field to populate based on the state and service level values entered. Should I write a query to get the results? Or should it be code in an event on a particular field? Whichever way works, any ideas on how to get started? Thanks. Gary:confused:
View Replies !
Link And Autofill A Name Based On NumberID
I have a main form for a tblCommittee and a subForm 'sfCommitteeOfficer' that will eventually take an entry or show -if its existing, a list of Committee officials. The officials come from a 'tblMembers'. There are too many members to use a drop down list box to select the MembNumber. What I wanted to do was be able to enter the MemberNumber in a txtBox 'MembID' on the form in the row containing Memb#, MembName, Position i.e chairman, Date elected Date retired, and have the members name automaticlly entered into the field MembName of the subform to limit the amount of entering the user has to do! Is this possible? I have a tblCommiteeType with key field CommitteeID, and foreign Key MembId, so I dont think I need a tblCommitteeMember because names are already stored in tblMembers.
View Replies !
Update Field Based On Other Other Fields
drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL. if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA" Right now I'm getting a circular reference error :/ please advise. Thank you.
View Replies !
Autofill Text Boxes Based On Combo Box
:confused: I have a combo box on a form that is used for data entry that allows the user to pick the date. This is based on a table that includes date, week ending date and month, all very specific for our business. I used a function that says Me.txtBox=ComboBox.Column1 for example. So my combo box would include all the information, and all but the actual date is not visible. I want this to feed into another table that will be used for reporting, and that table has the same fields of date, week ending date and month. I keep getting an error saying that the value selected from the combo box doesn't fit the criteria, and I believe what is going on is that it is taking all the fields instead of just the date. Any suggestions how I can still have the text boxes update if I only have the date in the combo box?:confused:
View Replies !
|
TRACKER
