I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions?
Thank you!
KellyJo
I am having trouble using a form as input for a query. The form uses multi select list boxes, with then intention being that if I select multiple fields then only records which include those fields will show (not only fields that contain those and no others).
I also have successfully worked up some keyword searches that I would like to have run on the same query. So say I want to search for two values in my list box, and it also needs to include keyword X... how would I run these all together, or is it possible?The form is "EVR Search Form"..The query is "EVR Query - Trending Filters" and I've also made a copy to test on, "Copy EVR Query - Trending Filters"
I have my database done, for the most part. But I want to add a lookup to a field in my Component table so that I can associate a particular number (paragraph number in a pub) to a failure mode description. In the Failure Mode table, I have associated a paragraph with a failure mode:
Example: 6-66.2Visual
6-66.8Continuity
6-66.8Insulation
Yes, some paragraphs have more than one failure mode. The primary key for this table is an autonumber which allows me to do this with minimal headache.
When I created a lookup within the appropriate field in the Component table, it erases my format/input mask and makes it look like this:
6662 Visual
6668 Continuity
I wasn’t able to put an input mask in the General tab of the FailureMode field in the Component table – I guess because it has the paragraph AND the failure mode in the lookup and 9-99.99;;_ just won’t cover the text for the failure mode description. The input mask is present in the Failure Mode table but isn't "crossing over" to the lookup!
I REALLY need those “masks” in the paragraph callout to help me distinguish and match the failure mode! Can someone please explain how to do this?
Thanks!
I have been editing the service call management database that comes with office XP.
I have a table called "Workorders" with fields:
WorkorderID (primary key)
CustomerID
And another table called "Workorders by Customer" with fields
CustomerID (primary key)
I want to be able to have an input form where I input a workorderID which then opens the "Workorders by customer form" by customerID which relates to the WorkorderID in "Workorders" which is typed in the input box.
Im only a novice and its the code part that im struggling with.
Any help would be great.
Hi,
Is it possible to create a lookup in a query against a table which has not relationship but has a match?
E.g. iif(spec(in query)=Spec (match table) then bring back cost in (match table)
Hope this makes sense?
Thanks
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
Hello, is there any way I can have my single-parameter query bring up a lookup list of options from a table field rather than relying on the user to correctly type in the criteria? Then when this query runs I will know the user is picking one of the correct options from the lookup list.
Thanks.
Using MS Access 2002
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number
DNIS - Text (This is the column i need filtered)
PropertyName - Text
ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
IN ([DNISList]![TFN])
A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.
Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.
I have a query which looks up records from a table, now what I would like to do is append the word All Projects to this query - is it possible, and if so how?
View 7 Replies View RelatedI have created a 'junction' table between 2 many to many tables:
Jobs table (details all jobs)Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)Stock table (details all stock).The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.
I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.
I assume it has to do with how the table was originally set up the field properties will need to be changed.
In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.
I am using
Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.
What am I doing wrong?
I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.
All I'm trying to do is a make table (table name to overwrite: "TBL_5_7_RFCs") query via VBA so I can loop through a set of VARs in a table. Here is my code:
Code:
Public Function test_sql()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
[code]...
I'll add the loop and array once I get this working but I'm stuck.
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
View 2 Replies View RelatedI have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
View 3 Replies View RelatedIs there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies View RelatedI have data recorded using an input mask,
"RF">L-0000;;*
to display data in the style: RFA-0001, RFA-0002 etc.
I have a make table query to join this field to another, to create a combo box look up.
Unfortunately, after I run the query, the only data from the input mask that gets imported is A0001, A0002 etc.
What have I done wrong? I guess the error must be in my specification for the input mask...
Any and all help gratefully received.
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
I have an App that runs a few action queries using:
Code:
CurrentDb.Execute "[My Query Name]"
At some point I get this Error: The Microsoft Access database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078).The query is there, I can run it from the DB objects window.Queries run using CurrentDb.Execute earlier in the code.
table name : Schedule
Field 1 = Vessel code
Field 2 = Voyage
Field 3 = ETA
Field 4 = berthed
Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
How could this be manage ?
Hi,
I'm trying my first query on the fly and am getting the above message when I try and build the query.
Ive posted the SQL below, can anyone tell me where I'm going wrong?
Private Sub Command24_Click()
Dim dbsCurrent As Database
Dim qdf As QueryDef
Dim strSQL As String
Set dbsCurrent = CurrentDb
Set qdf = dbsCurrent.QueryDefs("qrycurrentinvs")
Dim VendId As String
VendId = "Like '" & Me.Text0.Value & " *' "
qdf.SQL = "SELECT dbo_pmt_rqst.[vendor_id], dbo_pmt_rqst.[vendor_loc_code], dbo_pmt_rqst.[pmt_rqst_nbr], dbo_pmt_rqst.[pmt_rqst_date], dbo_pmt_rqst.[payable_entity_id], dbo_pmt_rqst.[ctrl_grp_id], dbo_pmt_rqst.[ctrl_grp_date], Sum(dbo_pmt_rqst_tax.[tax_basis_amt]) AS Net, Sum(dbo_pmt_rqst_tax.[tax_amt]) AS VAT, Sum([tax_basis_amt]+[tax_amt]) AS Gross, dbo_pmt_rqst.[pmt_ref_nbr], dbo_pmt_rqst.[pmt_ref_date] INTO tblinvoices" & _
"FROM dbo_pmt_rqst INNER JOIN dbo_pmt_rqst_tax ON (dbo_pmt_rqst.pmt_rqst_date = dbo_pmt_rqst_tax.pmt_rqst_date) AND (dbo_pmt_rqst.pmt_rqst_nbr = dbo_pmt_rqst_tax.pmt_rqst_nbr) AND (dbo_pmt_rqst.vendor_loc_code = dbo_pmt_rqst_tax.vendor_loc_code) AND (dbo_pmt_rqst.vendor_id = dbo_pmt_rqst_tax.vendor_id)" & _
"WHERE dbo_pmt_rqst.vendor_id" & VendId & _
"GROUP BY dbo_pmt_rqst.vendor_id, dbo_pmt_rqst.vendor_loc_code, dbo_pmt_rqst.pmt_rqst_nbr, dbo_pmt_rqst.pmt_rqst_date, dbo_pmt_rqst.payable_entity_id, dbo_pmt_rqst.ctrl_grp_id, dbo_pmt_rqst.ctrl_grp_date, dbo_pmt_rqst.pmt_ref_nbr, dbo_pmt_rqst.pmt_ref_date, dbo_pmt_rqst.pmt_rqst_date, dbo_pmt_rqst_tax.tax_basis_amt, dbo_pmt_rqst.pmt_ref_date;"
DoCmd.OpenQuery "qrycurrentinvs"
End Sub
Thanks
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
When I have a 3rd table looking at the row source of the 2nd table, which is looking at the row source of the 1st table. I only get numbers.
Everything I have read so far points to using a query as a solution. However, the query I made is not updating the information from the 2nd table.
I have attached a few examples.
Attachment 6247
this below shows. I changed "Bakersfield-test1" in the original table. However, the query I made does not update, still showing "Bakersfield-test". When you click on the drop down, it shows the updated "Bakersfield-test1", but it does not repopulate my column with the updated info from the table.
Attachment 6248
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.