Lookup In A Query With Unrelated Table??

Feb 29, 2008

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

View Replies


ADVERTISEMENT

How To Query Unrelated Table To Do A Calculation?

Apr 30, 2006

Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:

I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.

At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.

Can anyone help me?

Cheers

View 6 Replies View Related

Multiple Match Conditions In A Unrelated Table

Jan 31, 2007

Hi All,

My database has two tables:
1. Complete Address Data
2. Address conditions.

Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.

How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?

The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure

View 2 Replies View Related

Queries :: Joining Unrelated Data In One Table

Dec 26, 2014

I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.

View 7 Replies View Related

Query Dates With Unrelated Tables

Aug 31, 2004

I am trying to design a report that will Give information about down time and work time during the day.

I have three tables that I am trying to query:
TimeSheet - Employee name, date, etc.TimeSheetSub - Times worked (related to TimeSheet)Orders - Including Start and Complete dates and timesI want to find out on TimeSheet.Date Between TimeSheetSub.Arrived And TimeSheetSub.Departed how many minutes were worked on an order and how many many were not (Orders.Start And Orders.Complete).

When I try to combine these three tables in a query, though, it either ends with tons of records or doesn't work because the tables are unrelated. I was able to do it by making a subquery for the Orders part, but this will not work to make a report.

Any help on this matter would be greatly appreciated!

Regards,

Philip
Email Me
www.321green.com

View 4 Replies View Related

Lookup Value From A Table To Input In A Query

Oct 11, 2006

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

View 1 Replies View Related

Paramater Query That Brings Up A Lookup Table

Jun 21, 2006

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.

View 2 Replies View Related

Select Query Using A Lookup Table As Criteria

Aug 29, 2007

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])

View 3 Replies View Related

Tables :: Possible To Automatically Lookup Data In A Table Without A Query?

Jun 18, 2013

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.

View 1 Replies View Related

Queries :: Lookup Records From Table - Adding A Value To A Query

Feb 12, 2014

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 Related

Auto Lookup In Query Not Updating Underlying Table

Jan 6, 2012

I 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.

View 7 Replies View Related

Lookup Table - How To Make More Than 1 Field From Query Appear In The List

Dec 1, 2014

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.

View 1 Replies View Related

General :: Lookup List In Table From Query In Split Database

Sep 14, 2012

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 Related

Tables :: Split Database - Table Field To Lookup Values From A Query?

Jul 6, 2015

I 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 Related

Unrelated Tables

Feb 29, 2008

I have three tables DriversName, Route and Orderlines. My job, if I should accept, is to create a form for the dispatcher to assign a driver and a route to each order. Preferably in a query so I can create reports based on Route and Driver, for the loaders & production, and Driver and Route for the driver.

I have a nice query already built to capture the order lines to be delivered daily, averaging around 800 lines. My problem is relationship. There is no relationship to Orders from either the DriversName or Route tables. Unfortunately the Orders table cannot be added to as the next data download would just remove the fields. I have tried just to slam in the tables in my query but of course I get a repeat data line for every driver name and Route letter.

I need this form to have the dispatcher call up the order by number and have two comboboxes for them to lookup and assign order numbers to Driver and Route. Thanks in advance for any suggestions.

My question is, does someone have a suggestion as to how I could build a relationship of Drivers and Routes to the Order query?

View 9 Replies View Related

Unrelated Tables In A Form

Aug 26, 2004

Im a new user to access and I was wondering if someone could tell me if it is possible to put two completly unrelated tables into a form. All I want to be able to do is look at the information from both at the same time without having to switch between forms. Ill apreciate any help you can give. Thanks.

View 2 Replies View Related

Using Field That's Unrelated In A Report

Dec 6, 2005

I need to have a field show up in my report that is unrelated to all the other information in the report. It is a one-time entry that the person enters upon opening the database. It's basically the expected amount of work we'll be doing for the year. I need to display that at the top of my report and then utilize that number for calculations with other fields in the report to produce a couple of extra calculated fields in the report. I've tried it a few ways and I've either gotten #name? or #error.

Any ideas?

View 1 Replies View Related

Unrelated Subform Not Updating Automatically

Oct 22, 2006

This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.

I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.

How can I make the subform run a subroutine each time the parent form changes records?

View 4 Replies View Related

Chaning The Value Of A Field From An Unrelated Form

Jan 2, 2007

I have a 'Services' subform that allows a user to enter payment information, but the amount entered is over a specified amount then a field's value in another data is supposed to change (in this case, the 'Contract Status' should change from its default 'Active' to 'Void'). I was able to get an error message to appear upon this occurrence, but I have no idea how to change the value in the other table. here is my code:

Private Sub MaxComp_AfterUpdate()
If Me.MaxComp > 50000 Then
MsgBox "The specified amount is above the $50,000.00 limit. This contract will be flagged and reviewed by HCC to ensure compliance.", vbExclamation, "FMV Limit Exceeded"
'*** [sbfrmContracts]![Contracts]![ContractStatus] = "VOID"
End If

End Sub

***:This is the line I attempted to use to change the field value on the other table

Thank you in advance and happy new year.

View 14 Replies View Related

General :: Using Fields From Unrelated Tables

Jul 2, 2012

I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, I just need to know how many.

View 4 Replies View Related

Modules & VBA :: Restrict Value In Lookup Field To Only Include Values From Table B Not In Table A?

Feb 2, 2014

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?

View 5 Replies View Related

Modules & VBA :: Delete Record From Unrelated Form

Jul 2, 2015

I have a form based on Table A. When a yes/no tick is clicked in the form, I want to delete a record from Table B. The tables are joined by ID numbers.

View 1 Replies View Related

Queries :: Select Random Records From Unrelated Tables

Aug 9, 2014

I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:

Desired Result:
Animal Color State
cat red Ohio
dog blue Texas
fox green Iowa

I have been able to create three individual queries that will pull X number of random records by using:

SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());

Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?

View 14 Replies View Related

Forms :: Linking Two Unrelated Subforms To Main Form

Feb 5, 2014

I am trying to link two unrelated sub forms to a main form so I am able to query data all at once and make a report that displays all this data at once. I do not know if this is possible. I will tell you to the best of my ability about what I have going on.

My main form is a shift report. The primary key is a auto number ID. The rest of the fields are date, name, shift, vehicle. etc.

The first sub form is area attendance. Field are as follows auto number ID (primary key), report ID(which comes from the main form, linked), the area, and the area attendance.

The second sub form is the event log. Fields are as follows auto number ID (primary key), report ID(which comes from the main form, linked), time in, and events.

My relationship now is simply primary key from the shift report (the autonumber) going to the first and second subforms report ID's.

Problem is I can not query two distinct subforms like this (I realized).

View 1 Replies View Related

Modules & VBA :: Event Calls Seemingly Unrelated Procedure

Apr 11, 2014

I have two forms:

frmOpeartions
frmManagers

frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.

The code to open frmManagers is:

Private Sub lblNewManager_Click()
DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog
Forms!frmManagers!cboMoveTo.Visible = False
Forms!frmManagers!lblManagers.Visible = True
End Sub

Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:

Code:
Private Sub lblClose_Click()
DoCmd.Close acForm, "frmManagers", acSaveNo
End Sub

frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:

Code:
Private Sub Form_Close()
If CurrentProject.AllForms("frmPlants").IsLoaded Then
Forms!frmPlants!cboPlantManager.Requery
Forms!frmPlants!cboQCManager.Requery

[Code] .....

So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.

View 6 Replies View Related

Navigation Pane / Unrelated Objects - Access 2007

Feb 17, 2013

My Access 2007 database with one main table. There are associated queries and reports that sort under that table when I have tables and related views checked. The problem I am seeing is some clearly related objects fall under unrelated objects and not with the table.

Short of using a custom group and sorting them manually?

View 2 Replies View Related







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