INNER JOIN Based On Equivalent Values, Instead Of Equal Values

Nov 3, 2005

I'd like to create a query which will consist of simple SELECT statements as follows:

SELECT [table1].[field1], [table2].[field1], [table2].[field2]
FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);

The challenge arises b/c instead of joining on equal values, such as the following:
[table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]

I would like to join based on equivalencies, such as:
[table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;

I do not know the proper syntax, so this is where I need help. I tried to search online without any success.

I appreciate your help in advance.

View Replies


ADVERTISEMENT

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

Sep 18, 2013

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

View 2 Replies View Related

Value In A Table Equal To The Sum Of Other Values

Dec 11, 2007

Hey.

Is there any way to make a field in a record automatically set it self so it's the sum of other values in the record

http://img412.imageshack.us/img412/1677/helphg9.jpg

That's an example what i want to do except i had to manually type in 25. Is there any way to get that so it does that automatically. I am happy to use forms or macros or anything as long as it occurs straight away after the data is inputted and not have to run something or open something up

Thanks in advance

View 5 Replies View Related

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

Query To Return Values That Are Not Equal

May 25, 2006

I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.

Is there a way to return only the records that are present in the external database and not in my db?

Thanks

View 3 Replies View Related

Queries :: Criteria In Query Is Greater Than Or Equal To 15% But It Gave Values Less Than 15%

Nov 24, 2013

same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.

Link:

www dot dropbox dot com/s/n3iuev1c5e0dfby/StockMonitoring.accdb

xD: StockMonitoring.zip

View 5 Replies View Related

General :: Enter Number Equal To Or Over Ten Million - Getting Last Two Values Rounded

Feb 28, 2013

When I enter a number equal to or over ten million, the last two values get rounded and I don't want them to be. My field is setup as a single, standard, two decimals. When I tested it I typed in 123456789, what I got was 123,456,800.00

enter 9999999 get 9,999,999.00
enter 10000199 get 10,000,200.00
enter 10000001.75 get 10,000,000.00

Single is supposed to handle up to 10^38

I have checked my "Region and Language" settings for the OS (W7) and there is nothing in there about rounding or maximum number size. I would like to leave the data type at single for the space considerations, and because it should work as single.

View 8 Replies View Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

View 7 Replies View Related

How To Join Tables With Similar Values In Query

Apr 5, 2006

Can someone please help with the following:

I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.

Thanks

View 6 Replies View Related

Access Slow To Sum Or Join On Null Values?

Oct 5, 2006

Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.

View 7 Replies View Related

Forms :: How To Join (concatenate) Records Of Values In Single Line

Jan 15, 2015

In Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).

Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...

This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:

"These were the votes of the owners of fractions:

O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78

How can I do it?

View 1 Replies View Related

Modules & VBA :: Join Multiple Values Into String For Summarizing Data On Reports And Exports

Mar 14, 2014

I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.

My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.

Here is an example:

Public Function SO_Description(intSO As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLSelect As String
SO_Description = "Profiles: "
Set db = CurrentDb

[Code] ....

View 1 Replies View Related

Reports :: Report In Which A Textbox Generates Numerical Values And Letter Values

Aug 6, 2014

I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.

View 9 Replies View Related

Modules & VBA :: Get Number Of Unique Values Without Separating Values That Belong To Same Block?

Dec 5, 2014

I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?

So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.

If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.

For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.

So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.

There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.

A few notes.

1.Getting as close as possible to the requested number is a requirement.

2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).

my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134

[code]....

A few sample outputs

one solution is taking rows 1-6 and 26.

OtherID
11098554
56200936
25138850
56148974
56530104
56148975

Another solution is taking rows 1-4 and 10-14.

OtherID
11098554
56200936
25138850
56024315
56115247
56148974

This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.

View 12 Replies View Related

Forms :: Default Values Disappear If Put Values In Text Boxes

Sep 17, 2013

In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).

Problem: The default date shows up perfectly until a value is put in any of the other text boxes.

For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.

View 4 Replies View Related

Forms :: Allowing Different Values In One Field Depending On Selected Values Of Another

Nov 22, 2013

I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:

Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"

[code]...

View 14 Replies View Related

Quering Based On Distinct Values

Aug 24, 2005

Hi all,

I had table with following data

Table

f1 f2 f3
1 10 AB
1 11 WW
2 10 DD
2 11 DD

f3 is memo field

I had to retrieve data by grouping records based on f1 value

and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.

I should get output like this

f1 f3
1 -
2 DD


I tried a lot by taking the distinct count of the records and displaying - when count is > 1

but i am not able to succeed.


please any one give the solution.

waiting for your help.


Thanks

View 2 Replies View Related

Automatic Values Based On Other Fields

May 18, 2007

I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number.
Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks!

Example:
If I entered:
List # Box # Barcode #
77 4
It might not know what the barcode is the first time. But once I had entered
List # Box # Barcode #
77 4 366582
It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like:
List # Box # Barcode #
77 4 366582
77 4 366582
...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.

View 8 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related

Queries :: Rename Field Values With Values From Another Table

Mar 2, 2015

I have two tables.descriptions I'd like to relate and use to find/replace in bulk.

[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)

I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".

Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico

[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico

View 4 Replies View Related

Deleting Duplicate Values Based On 2 Fields

Jan 3, 2006

Hi guys,

I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.

companyname zip
billybobs 68135
dilberts 68137
billybobs 68135

what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?

Any help is greatly appreciated

Thanks

James

View 14 Replies View Related

Adding Value In Table Based On Values In Two Other Fields

Dec 3, 2007

Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

View 12 Replies View Related

Automatic Values Based On Entered Data

May 9, 2005

Hi all,
Please bear with me if I'm asking a dumb question. I've been tasked to create a database from scratch and I've not had much experience working with Access.

**My Question**
Using a form is it possible to have the form automatically enter a "value" in one field based on the information inputed by someone in a different field.
ie. When some one puts in a value between 0 and 20 in one field it automatically enters a 1 in the other, and when some one puts in a value between 21 and 30 you get a 2 and so on down the line.

Thanks for the help.
Cheers

View 6 Replies View Related

General :: Determining The Values In One List Based On Another

Jun 27, 2012

I have a table with a few Listbox options. I want one of the columns' values to be determined by the value chosen in another field. How do I do this?

Example: In column A I choose value ABC from the dropdown. I want column B to now offer values in that list of DEF, GHI, JKL. If I chose XYZ in column A instead, I'd want the column B list to show MNO, PQR, STU. Etc.

View 6 Replies View Related

Queries :: Sum Values Based On Multiple Criteria

Aug 12, 2013

If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?

example

Tbl 1
PK, Month
Percent

Tbl 2
FK, Month Enrolled
Qty of Rx in the 1st month enrolled
Qty of Rx in 2nd qtr
Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)

If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent

Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.

I only know how to create Query's using the design mode. I dont know how to write SQL statements.

View 1 Replies View Related

Queries :: Averages Of Values Based On Dates

Aug 20, 2013

I'm not sure if this is the right forum. If not let me know and I'll move the thread.

Table 1
Date
Measurement A
Measurement B
Measurement C

Table 2
Table 1 ID Link
Data 1

Tables are linked 1-to-many from table 1 to table 2 by ID.

I would like to average the Data1 data per Table1 ID and report it with the Table 1 Measurements.

View 1 Replies View Related







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