Query To Return Values That Are Not Equal

INNER JOIN Based On Equivalent Values, Instead Of Equal Values
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 !   View Related

---

Query To Return Intermediate Values
Hi All,

I need an Access query to return the rate at which a customer should be charged. A customer's rate can be set for a period of 1 to 6 months and the customer's rate table could look like this:

ACCOUNT_ID........START_DATE........END_DATE...... ..COST_PER_UNIT
31308.................01-Apr-07................30-Jun-07.................6.195
31308.................01-Jul-07.................31-Aug-07.................6.304
31308.................01-Sep-07................31-Mar-08.................5.422
31308.................01-Apr-08.................31-Aug-08.................6.304

I need a query (preferrably SQL) that will return the rate a customer should be charged each month, e.g.

31308 / Aug 07 / $6.304
31308 / Sep 07 / $5.422
31308 / Oct 07 / $5.422
etc, etc

I'm using Access 2003 and the rates table contains details of about 2500 customers.

Any ideas would be welcomed.

PS. Can someone tell me how to paste a table straight from Excel into the forum post body???

View Replies !   View Related

---

Query Using Two Tables Does Not Return Values For Certain Parame Version: 2002
Hi

I have a query that runs off three tables: These are tbl_contacts, tbl_status, tbl_publications. All contacts have a status but not all will have a publication. The problem is that when i run the query on a status type where none of the contacts for that status have a publication it will return no results, where what I actually need is for it to just return the details of those contacts for that status. The whole query runs off a form with combo boxes.

grateful for any advice
Shapman

View Replies !   View Related

---

Value In A Table Equal To The Sum Of Other Values
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 Replies !   View Related

---

Query To Return Default Set Of Values In A Subform When Main Form Opens
Here is my sample DBA

I'd like to solve this issue

when I open my Quotations form I'd like a set of services to be selected by default, ex: Brush and Pen. So the user only has to input the quote amount

is there anyway to have the form open with a script like:
"on open (or on load). in subform SELECT refproduct where product from tblproducts = 1 and also SELECT refproduct where product from tblproducts = 4"
Therefore when my main form opens, in the subform I have already Brush on the 1st line selected and pen on the 2nd. My user only has to type the price.

Thanks for your much appreciated help

View Replies !   View Related

---

"Not Equal And Not Equal" Query Not Working
I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)

The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.

The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).

What am I doing wrong? Thank you in advance for any suggestions.

View Replies !   View Related

---

Return Different Values......
Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)

I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?

I am sure this is fairly easy.......it has just got me stumped!!

Many Thanks.

Frank.

View Replies !   View Related

---

How To Return Values From Multiple IDs On The Same Line
I'm working on a freebie for a day care center.

I can not seem to work this out in a single query.

I have:
tblChildren
tblParents
tblGuardians

Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.

Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.

each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.

So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.

Has anybody got any guidance for me please?

View Replies !   View Related

---

Not Equal To & Grouping Query
I have Table as PL2008 and RPT2. Both tables have one column named PTUNID. My goal is to first Group, sum certain Columns in the PL2008 then match PTUNID from this Grouping to RPT2 Table's column PTUNID and then show the results which do not match.

I have written following Qry but what really happenning is its giving me all the results ( match and no match in both tables ) and Sum values are incorrect.

SELECT [PL2008].PTUNID, Sum([PL2008].[Total Hours]) AS [SumOfTotal Hours], Sum([PL2008].[Actual Cost]) AS [SumOfActual Cost], [PL2008].[PRFIELD]
FROM RPT2, [PL2008]
WHERE RPT2.PTUNID <> [PL2008].PTUNID
GROUP BY [PL2008].PTUNID, [PL2008].[PRFIELD];


Any suggesstion



Thanks
Niri

View Replies !   View Related

---

Return Only One Record From Table With Repeating Values In Fields
I have a table like this


Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...


I want to do a query to return just one row per unique ItemNo

So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog

I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.

I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.

View Replies !   View Related

---

Select Equal Type - Based On Random Query
Good Afternoon Everybody,

Apologies for imposing on you all but I was wondering whether or not you could help me out??

Would you be so kind as to kind as to show me a practical example of how to incorporate a facility into my attached Database which serves to select an equal ratio of my field “SectionHeadings” such that out of the 30 “random” questions that it currently generates it will provides me on completion with the following:

5 From Set1
5 From Set2
5 From Set 3 …..etc etc

It currently generates the 30 questions that I need without a problem; however it routinely selects any number of one particular type, sometimes more from one subject and less of another.

All subjects headings are of equal importance to me and as a consequence would be very grateful if you could look at what I’ve already done and show me what needs to be done in order to achieve my aim.

I hope my request is possible - Any help you could give me would be very much appreciated. Thank you very much.

Best Regards

CarolW

View Replies !   View Related

---

Trimming Query Return Value
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks

View Replies !   View Related

---

Need Query To Return Same Record Twice
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?

View Replies !   View Related

---

Time Return Query
Hi Everyone,
I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.

AM=700-1399 PM=1400-2299 NIGHT=2300-699

View Replies !   View Related

---

Query To Return Table Name As A Value?
Hello all,

Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found?

The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab.

I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - :confused: but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.:confused:

Any suggestions, if able, would be appreciated!

View Replies !   View Related

---

Wrong Query Return
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.

View Replies !   View Related

---

Query - Weird Decimals Return
I enter values into a table with 2 decimal places.

I have a query that applies percents to the values - I have set the results to have 2 decimal places.

I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.

It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.

Can someone please help.

Thanks!

View Replies !   View Related

---

Query That Return No. Of Records In Run Time
Hi all,

May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.

Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.

Thanks !!

View Replies !   View Related

---

Blank Or Duplicate Return From Query?
I have searched for an answer and spent some hours but still not getting it.

I have played about with the query joins and still nothing?

I have never had this problem with my database in 5 years (well not that i have noticed).
Some records are fine others are triplicated and quite a lot are blank?

This is the SQL for the query.

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Address, Employee.[Town/City], Employee.Region, Employee.DateofBirth, Employee.Nationality, Employee.MaritalStatus, Employee.PassportNo, Employee.ExpiryDate, Employee.Nextofkin, Employee.Relationship, Employee.NOKAddress, Employee.ContactNo, Certificate.CertName, EmployeeCert.Level, Trade.TradeName
FROM Certificate INNER JOIN (Trade INNER JOIN ((Employee INNER JOIN EmployeeCert ON Employee.EmployeeID = EmployeeCert.EmployeeID) INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID) ON Certificate.CertID = EmployeeCert.CertID
WHERE (((Employee.EmployeeID)=[Forms]![Employee]![EmployeeID]) AND ((Certificate.CertName)="IRATA"));

Basically i have to solve this problem.

I have a report that is based on this query and if the employee is not displayed correctly instead of the employees Name and Surname i am getting "#Error" ?

Many thanks in advance

View Replies !   View Related

---

Query Doesn't Return Any Fields
I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."

What am I doing wrong?

Screencaps attached.

Thanks.

View Replies !   View Related

---

Query To Return Computers Without Allocation
I have 2 tables tblComputers & tblComputerAllocations
tblcomputers has 85 records & tblComputerAllocations has 71.

I would like a query that returns the 14 computers that have no allocations.

I tried a union query on field ComputerID but i must misunderstand its use!!
Thanks in advance,
Phil.

View Replies !   View Related

---

Query Parameter To Just Return Weekdays
I am trying to write a query that looks at groups of records for averages and maximums but the records are only from Mon-Fri (Trading Days). I have a query that runs everyday and compares today's close to see if it is greater than the maximum close of the last 20 trading days. I want to use between date()-21 and date()-1. BUT this will include weekends where there is no data.

QUESTION: Is there a way to say between the last 20 trading days(Mon-Fri)?

I researched dateadd() and found one place were it says that "w" is used for weekday. http://www.database-design-2000.com/dateadd.htm BUT when I did a simple query to see if it would exlude the weekends it doesn't. I will have no data for the weekends but if I am doing an average of the last three days and this is run on Monday, it will only include Monday and not the previous Thursday and Friday. Thanks for your help:)

View Replies !   View Related

---