473,856 Members | 1,754 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

limit query results to no duplicates on 1 field

Dom


I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.

the first query returns a number of data items, consisting, among other
fields, of a Company_ID and a Rating_Date. For most companies, the
latest value of Rating_Date is equal to a value in a seperate table
(tblVariables) which logs the last time a ratings update took place on
the database. To return one line per company for these companies, the
second query has a condition

...
WHERE (((qryCompanyIn fo.Rating_Date) =[Variables]![LastRatingDate] ...

However, this does not show companies which are no longer being rated,
and therefore have no record where Rating_Date = LastRatingDate.
Companies which are no longer being rated have a Company_Type value of
9, so to show these companies, I added to the SQL as follows:

...
WHERE (((qryCompanyIn fo.Date)=[Variables]![LastRatingDate] OR
(((qryCompanyIn fo.Company_Type )=9));
...

The problem is that this now returns 1 line for all rated companies,
using the first condition, but then every record for the unrated
companies. I need to somehow filter the second condition, so that only
1 instance of each company appears in the final query.

I hope somebody can help on this, as I've been going around in circles
trying to figure it out. Thanks

dom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
2 18706
Dom,

If I understand you correctly, you need to use an "Outer Join". You can
do this by opening your second query in design view and double-clicking
the black join line connecting qryCompanyInfo and tblVariables.

Choose the option: "Include ALL records from 'qryCompanyInfo ' and only
those records from 'tblVariables' where the joined fields
are equal."

The resulting SQL will look something like:

SELECT qryCompanyInfo. Company_ID, qryCompanyInfo. Rating_Date,
tblVariables.La stRatingDate
FROM qryCompanyInfo LEFT JOIN tblVariables ON qryCompanyInfo. Rating_Date
= tblVariables.La stRatingDate;

HTH

Stuart

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2
Dom <cr************ **@bilge.net> wrote in message news:<3f******* **************@ news.frii.net>. ..
I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.

the first query returns a number of data items, consisting, among other
fields, of a Company_ID and a Rating_Date. For most companies, the
latest value of Rating_Date is equal to a value in a seperate table
(tblVariables) which logs the last time a ratings update took place on
the database. To return one line per company for these companies, the
second query has a condition

..
WHERE (((qryCompanyIn fo.Rating_Date) =[Variables]![LastRatingDate] ...

However, this does not show companies which are no longer being rated,
and therefore have no record where Rating_Date = LastRatingDate.
Companies which are no longer being rated have a Company_Type value of
9, so to show these companies, I added to the SQL as follows:

..
WHERE (((qryCompanyIn fo.Date)=[Variables]![LastRatingDate] OR
(((qryCompanyIn fo.Company_Type )=9));
..

The problem is that this now returns 1 line for all rated companies,
using the first condition, but then every record for the unrated
companies. I need to somehow filter the second condition, so that only
1 instance of each company appears in the final query.

I hope somebody can help on this, as I've been going around in circles
trying to figure it out. Thanks

dom

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


hello,

Why do you make the rather complicated construct of joining with a
separate query containing records with the last ratingdate ?
Didn't you try to use aggregate functions like Max(ratingdate) to
filter out the latest ratings for each company ? You just have to make
a query consisting of some company-idtifier and the ratingdate to
achieve this (and eventually some other conditions ..) and make it an
aggregation by pressing the sum-button (the greek E-like symbol on the
menu) and to select max in stead of 'group by' for the ratingdate.
After that you can join this query with other data based on the
company-id and the ratingdate to get other relevant data by using the
query again as a table for another query.
May be you tried this procedure, but then you had to make a separate
small aggregation-query for companies not rated with some dummy or
empty value for the last rating-date if these are not already result
of the other aggregation-query for some reason and make a union, but
this method is not an elegant one.
Often this can be avoided by ensuring all companies are retained in
the first query when these are joined with other tables. In that case
you have to connect the other tables with an outer-join (selecting the
connection-lines and changing the jointype so the companies are all
kept into the query and also adding a 'or is null' clause for every
select-condition for fields in the tables connected by the outer
join-conditions. If the companies not rated are retained, the
resulting aggregation-query automatically produces null-values for
company's without any ratingdates.
May be I dont quite understand why it seems so difficult, but I hope
you get some suggestions to find a more elegant solution.

Marc
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5627
by: Guy Erez | last post by:
Hi, I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy.
1
1435
by: AP | last post by:
We commonly use this method to "pivot/crosstab" table results. For example lets say there are two offices, and we want the total sales for each ofice in its own column(Next to each other). We do not want to use a crosstab query so please do not recommend it. Typically we do this. If the data is structured as follows Office Sales Month A 5006.432 1/1/02 B 5056.987 1/1/02
6
17166
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how this can be done? I've tried setting the default value of the text fields on the form to be equal to ! using Access' expression
0
1912
by: Rob | last post by:
I doubt this is the best way to do it, but what I came up with was to hide the XML in an HTML Comment then edit the file deleting the HTML stuff and keep the XML results. If anyone has a better solution, I would be interested. Thank you. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cmd As System.Data.SqlClient.SqlCommand cmd = New System.Data.SqlClient.SqlCommand
3
4831
by: Sim Zacks | last post by:
I am using 8.0 beta 1 on an RH 8 Linux server. I have a union query that I am converting from access (where it worked) and it is returning duplicates. The only difference between the two rows is the Row field, which is returned automatically. and an example of a row that it has returned duplicate. I have verified that the row only shows up 1 time in each select statement when run individually.
9
3067
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
2
1953
by: milo1955 | last post by:
I have a form that passes a single variable via the GET function to a mysql databse query with the results paginated. The first page of the results work fine, but the link to the second page yields the page 2 results of the entire table. I'm guessing that the variable hasn't passed to page2 query? Please excuse my ignorance. Here's my code: <?php //where $rory is the variable that is passed via the form with the GET action $page = $_GET...
1
1821
by: DAHMB | last post by:
I have a column in a report that I want to return a sum of certain criteria from a query between two dates. I set the dates in a form that I launch the report from. My query contains a field called ClearanceCode. The ClearanceCode field contains multiple entries of the following data: "A", "B" "C", "D","E", "F", "O", "U", "W". The column I am trying to fill is only one of 5 and the criteria is different for each column and therefore I can...
16
7973
by: dougmeece | last post by:
Good day everyone, I have a database with 2 main forms. The first form is used to add records to the database and contains a command button that opens the 2nd form for records searching. On the second form I have tow combo boxes that I would like to search from. Currently, I just have the search button run a query which opens in a separate screen. This is the same for both combo boxes (they are independent of each other). I would like...
0
9758
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10693
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9527
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7929
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7087
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4571
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4170
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.