QRY To Get Values From 2 Tables

Sep 26, 2006

Hi all....

I have been beating my head for 2 days on this. Any help will be GREAT!
I have 2 tables: CLIENTS and RELATION

NAMES has the client_ID, first_name and last_name along with other information for clients. RELATION is how the clients fit with each other. RELATION has relation_ID, Relations_description and up to three fields where we can enter the ID of clients from the client table called Rel1_ID, Rel2_ID, and Red3_ID. Now all the three rel_IDs come from CLIENTS and stores only the client_ID. There are multiple relationships.

How can I display the names and not the IDs for the clients?

I am able to display only one client name. I need this for form as well as report. I can do dlookup for form but don't know for reports. I am thinking that if I have it in the query, then I can use that for the form as well as reports.

I am able to show the the name for one client by running the following qry. But I am not able to modify that to show the other 2 names.

SELECT RELATION.Team_Name, RELATION.relations_Description, RELATION.rel1_ID, RELATION.rel2_ID, RELATION.rel3_ID], [client.f_name] & " " & [Client.l_name] AS Client_Name
FROM RELATION, CLIENTS
WHERE ((([relation.rel1_id])=[client.client_id]));

View Replies


ADVERTISEMENT

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

View 5 Replies View Related

Tables :: Count Unique Values With Different Values

Feb 7, 2014

i have one table in which ID is Primary ID with Different Values

Like

ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U

it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.

View 8 Replies View Related

Tables :: Passing Field Values Between Tables

Dec 10, 2014

Currently, we have a table which contains a field for each type of training that can be taken (FUT1, FUT2, ...). Once an Instructor has taken one seminar, the table is populated with the date taken, via their employee number (the key) through an update form.

Now, the company wants the paper training reports to turn into a digital part of this database. As envisioned, the form and associated table will have combo boxes with pre-determined training types (FUT1, FUT2, ...). The date would be typed in.

The problem I see is getting the dates and training type from the future table to populate the existing table in the correct field for the correct Instructor.

In addition, there exists a report which prints out the training dates for each Instructor at each school. This is will still be in use.

View 7 Replies View Related

About Unique Values In Tables!!!

Oct 4, 2006

Hi,
I have a situation and i do not know how to handle it.
I am developing an application for a lawyer. In the data base i have the following tables: Files, the people table (that are involved in a lawsuit), and other tables. The problem is the following: in the people table, beside the primary key i have another unique element that is social security number.
In the files table beside the primary key another unique element exists: the number and date of the file (File 1 from 15.02.2006). A lawsuit file can have more law terms: today it has one law term and maybe next week another law term so i must have the same file many times in the table with the same code and another date, but i cannot add the same file many times in the data base because a file has data about one person and the social security number of the person is unique.

If anyone can offer me a solution it would be good.
Thank you!

View 2 Replies View Related

Matching Values From 2 Tables

May 4, 2007

I have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon

View 1 Replies View Related

Tables And Calculated Values

Oct 14, 2005

I have been reading previous post s regarding the storing of calculated fields. Basically everybody says don't do it. However, if I don't I am not sure how to achieve what I need to achieve.

I am storing data on tool vibration levels. Data is input for X, Y and Z axes and a vector sum (total) is calculated on the fly using a query. So I only ever store X,Y and Z.

Ultimately I am looking for this database to be able to give me useful statistical outputs in particular a range of vector sums for a tool type. If I don't actually store the vector sum for each test then how I am ever going to report a range? The data would never change for each test as these become statictics, it's not the same as working out rates for example which may vary according to parameters.

What is the best way to do this?

View 2 Replies View Related

Dynamic Values In Tables

Dec 13, 2006

Hello everybody! i want to create a table consisting of 2 columns. one column may contain constant values and the other has to contain dynamic values (values of the second column must depend on the values of the first column). For example if the constant of the first column is ID then the variable of the second column will be ID+5. Something like that below

Column 1 Column 2
ID1 ID1+5
ID2 ID2+5
ID3 ID3+5
. .
. .
. .

Could anyone give me a tip about that? Thank you in advance...

johann

View 2 Replies View Related

Tabstrips And Values From Different Tables

Apr 13, 2005

I havent used Access 2003 much before (or any other version) but i have to produce a database for a vehicle restoration place to hold details about the client, their vehicle, stock etc...

I would like to use a tabstrip for navigating the DB with tabs for each section. E.g. a Client Info tab, a vehicle info tab, one for parts and stock and so on.

I have designed a form with a tabstrip on and am having problems when it comes to showing the information on each page. So far I have text boxes to edit the client details on the first tab page, but then when i come to the second, i am having trouble getting it to display/update information from another table.

Can anyone advise me what i should be doing to make it work, or should i try a different design?

View 6 Replies View Related

Help ! Display Values From Tables!

Sep 7, 2005

Hi guys,
I need urgent help on a simple question. I hope you will be able to help me.
I have a table CustomerMaster which stores customer number and name. (Customermaster)
I have another table which stores the product details for customer. (customerProduct)
I have another table which shall store order details. (CustomerOrders)
On the form, the user selects a customernumber, the system then displays the customer name.
The user then picks up the product ordered by customer (picked from combo box). On the combobox on recordsource I have given a select query which fetches the records matching the customer selected from table
Now I want the Product Price, tax and duty of the product to be displayed for the selected product. Somehow, I have to again make that as a combobox and then select the price (although there is only one record...
Can anyone tell me how to do this?
Regards
K

View 14 Replies View Related

Passing Values Between Tables

Aug 18, 2004

Hello,
I would like to know how to pass a value from one table to another within the same db based on a matching username. I need to update a column called 'store' in one table with the values of another column called 'value' from a different table.
Thanks for any help in advance.

View 5 Replies View Related

Tables :: Accepts Only Certain Values?

Jul 1, 2013

I want my table to accept only certain values in a field. I tried different things with no luck.

Field: OCC (except only 1 or 2)

I tried Validation Rule using Expression Builder, which did not work for me.

I also tried creating a default value of the same field. What I'm trying to do is have 1 come up as a default, and have the ability to change it to 2 if needed. But only have the ability to type in a 1 or 2.

View 6 Replies View Related

1 Combobox With Values From 2 Tables?

Apr 23, 2013

I Have to tables, Employee's & Vehicle's. I'm making a dispatch form. and i want to put a "Unit Dispatched" combo box that will populate from the Employee's ID and the Vehicle ID. We sometimes dispatch employee's to a job on their own and sometimes we send a company vehicle. is there any way to do this?

View 1 Replies View Related

Matching ID Values Between Two Tables

Jun 4, 2014

I have two tables, one has two fields:student ID and student name.its kind of like this:

1 Mark
2 Tom
3 Franklin

the other table has three fields: student name, student classes, it goes like this:

Mark calculus
Mark Biology
Tom Statistics

Franklin Calculus
Tom Chemistry

what I want is for the second table to have its related id from the first table so it could be like this:

1 calculus
1 biology
2 statistics
3 calculus
2 chemistry

I cant simply make find and replace because the records are a lot is there another way should I relate the tables of something how will it work?

View 8 Replies View Related

How To Combine The Values Of Two Tables

Jun 27, 2012

Here I have two tables. The first one categorizes items by certain descriptions from a fixed list. Each description, eg; "Big Size" is categorized in the table below with a minutes key. I want to create a table/query that is of the form of the 3rd table. I showed only the first record as an example, replacing minutes with each description.

Item
Size
Strength
Agility
A
Big
Strong
Slow

[Code]....

I want to "combine" these tables to make the table look like this without changing the design of the above two tables. If I MUST change the design of one of the tables, that's fine.

Item
Size
Strength
Agility

A
3
1
10

View 3 Replies View Related

Update Values In Multiple Tables

Oct 26, 2005

I am developing an access db where employees are allowed to load tools from a tool store.

I have three tables: tblEmployee, tblTool, tblOnLoan.

tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.

tblOnLoan is used to record which employee has what tools on loan.

When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.

When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.

i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.

Can anyone please help?

View 1 Replies View Related

Combo Box Values Being Stored In Tables As 1 And 2 Etc..

Feb 12, 2008

Hi,

I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...

View 2 Replies View Related

Auto Values In Access Tables?

Nov 7, 2006

I am writing an access database which has to store properties for an estate agent. They have a PropertyID field which is the primary key in this table. The ID is always in the following format PL219AD23 (postcode PL21 9AD and house number 23) Is there a way that once an address of a property is entered then the ID is generated from that? Or do they always have to enter the property ID themselfs.

Any help or tips would be appreciated..

Thanks

Fraser

View 4 Replies View Related

Insert Values Automactly In Tables

Nov 22, 2006

Hi, this is my first post in this forum... so thanks all for your answers...

Ok, the problem is this...

I've got a database with some tables, but in that database i've got 3 important tables....

Lets refer that the tables have this names [Table1,Table2,Table3];

In the Table 1, there are 3 important variables, one of them its a primary Key, ok all i wanna know how to do... its this:

When i insert a new regist in this table, the first 3 values from the first three colunns must be replicated to table2 and table3.

Can you help me PLEASE!! :(

View 10 Replies View Related

Query On Tables With Null Values

Nov 9, 2005

Hi,
I have a problem with one of my query, the query has 2 tables, the secondary field from Table2 is linked to the primary field of table1.
The primary field Doesn't allow null Value
The Secondary Field does allow null value which means that the Data in table 1 only concern Some of Table2's Data.
If I run the query with the two tables I can only see the records with the secondary field with no Null Value.
Is it possible to get the query Showing All the records of table1 2 and Table1 even if Table1 has no values?

View 1 Replies View Related

Inserting Values Into Multiple Tables

Aug 22, 2005

I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!

I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials.
I have three Tables: tblX, tblY, tblZ.
tblX stores details for each individual material. Its structure is as follows:

tblX
XPK | ID | value1 | value2 |
01 | A | asdfas | asdfdf
02 | B | dfasdfa | sdfaf
03 | C | asdfffd | asdfg
(here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)

tblY stores materials that are combinations of two primary materials:
tblY
YPK | 1st | 2nd | ID | value1 |
101 | 01 | 03 | M | keiury |
102 | 02 | 03 | N | kjgeiih |
103 | 03 | 01 | P | djlkgoi |

(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)

tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:

tblZ
ZPK | 1st | 2nd | 3rd | 4th | ID | value1 | value2 |
1001 | 01 | 03 | -- | -- | R | asdprw | mnvd |
1002 | 01 | 102 | 02 | -- | S | adsfd | oirtyr |
1003 | 103 | 02 | -- | -- | T | werwq | pojfgr |
1004 | 02 | -- | -- | -- | U | alkfdp | uioite |
1005 | 01 | 02 | 103 | 102 | V | keqwei | oirewj |

(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)

So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...

I hope I am clear enough till this point.

My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:

1) save each primary material from tbltemp to tblX
2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.

I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.

I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).

If you are still reading, I already owe you a ton of thanks!!

View 7 Replies View Related

Combo Box Values Being Stored In Tables As 1 And 2 Etc..

Feb 12, 2008

Hi,

I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...

View 1 Replies View Related

Tables :: How To Validate Values In List

Dec 31, 2013

I want to validate the value of the list in the lookup in the table and create a list of the values that no match the values of the list..

View 3 Replies View Related

Tables :: Storing Calculated Values?

Mar 7, 2014

I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.

attached is my table and Input Screen.

View 3 Replies View Related

Modules & VBA :: Color Box With Tables Values

Oct 7, 2014

I have a calendar to let the users pick up the days and will be save temporaly in a table to print but i only can display the current mouth in calendar, went the user change the mouth the color of the boxes change to normal,in attache i send the BD, In resume, the users select the days in calendar and went change from one mounth to another the days picked stay with color.URL...

View 7 Replies View Related

Tables :: Change Default Values?

Aug 21, 2013

Access 2010. I inherited a database that I need to copy and get it ready for next year's data. There are several places in the various tables where the default value on a field is 2013. I need to change to 2014. Any vba code or macro that I can do this without opening all the tables to change the values?

View 10 Replies View Related







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