Hi All,
I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only fields
ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he should
be listed only once).
Any help appreciated
Regards,
Nicolae 4 2056
I think I managed to build this query by myself, using the sample query
which John Winterbottom had the kindness to show me a few days ago.
SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address = a.Address AND b.Name = a.Name
);
Regards,
Nicolae
"Nicolae Fieraru" <no****@please. cxm> wrote in message
news:40******** @duster.adelaid e.on.net... Hi All,
I have a Table1 with ID, Name, Address, Purchase. I want to build a query which shows all the records from Table1 (only
fields ID, Name, Address), but there shouldn't be listed records which have the same Name and Address (if a customer made two different purchases, he
should be listed only once). Any help appreciated
Regards, Nicolae
"Nicolae Fieraru" <no****@please. cxm> wrote in message
news:40******** @duster.adelaid e.on.net... Hi All,
I have a Table1 with ID, Name, Address, Purchase. I want to build a query which shows all the records from Table1 (only
fields ID, Name, Address), but there shouldn't be listed records which have the same Name and Address (if a customer made two different purchases, he
should be listed only once). Any help appreciated
without the ID column you can do:
select [Name], Address
from Table1
group by [Name], Address
if you have more than one purchase for a customer, does it matter which ID
is returned? And if it does, what criteria will you use.- i.e. the first,
the last etc..
Hi John,
The real table actually has more fields than the one in my example. I need
to show in my query some of the fields. These fields contain information
about the customers mailing addresses. I don't need any other details from
the rest of the fields and I don't care too much which of the records will
be shown in the query (although I think the earlier ID is preffered).
It seems I oversimplified my problem and this causes some problems.
Here is an example closer to my real problem:
Table 1 with ID, FirstName, Surname, Address1, Address2, Suburb, State,
Mailout, Purchase
I need all the fields from all records from Table1 with unique Surname AND
Address1 (there are customers who made multiple purchases and they are
recorded in the table multiple times. But for example Address1 or State
might not be filled in into some records, while in others it is)
The query I built
SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address1 = a.Address1 AND b.Surname = a.Surname
);
works fine for my sample table with 10 records, but stops responding for the
real table, with 228000 records...
(I run a sample database under Access97 on my computer, where I test my
queries and the real database on Access2002 on a remote computer, using
Remote Desktop Connection)
Regards,
Nicolae
"John Winterbottom" <as******@hotma il.com> wrote in message
news:2j******** ****@uni-berlin.de... "Nicolae Fieraru" <no****@please. cxm> wrote in message news:40******** @duster.adelaid e.on.net... Hi All,
I have a Table1 with ID, Name, Address, Purchase. I want to build a query which shows all the records from Table1 (only fields ID, Name, Address), but there shouldn't be listed records which have the same Name and Address (if a customer made two different purchases, he should be listed only once). Any help appreciated
without the ID column you can do:
select [Name], Address from Table1 group by [Name], Address if you have more than one purchase for a customer, does it matter which ID is returned? And if it does, what criteria will you use.- i.e. the first, the last etc..
Nicolae Fieraru wrote: Hi John,
The real table actually has more fields than the one in my example. I need to show in my query some of the fields. These fields contain information about the customers mailing addresses. I don't need any other details from the rest of the fields and I don't care too much which of the records will be shown in the query (although I think the earlier ID is preffered). It seems I oversimplified my problem and this causes some problems. Here is an example closer to my real problem:
Table 1 with ID, FirstName, Surname, Address1, Address2, Suburb, State, Mailout, Purchase
I need all the fields from all records from Table1 with unique Surname AND Address1 (there are customers who made multiple purchases and they are recorded in the table multiple times. But for example Address1 or State might not be filled in into some records, while in others it is)
The query I built
SELECT a.* FROM Table1 AS a WHERE a.ID = ( SELECT min(b.ID) FROM Table1 AS b WHERE b.Address1 = a.Address1 AND b.Surname = a.Surname );
works fine for my sample table with 10 records, but stops responding for the real table, with 228000 records... (I run a sample database under Access97 on my computer, where I test my queries and the real database on Access2002 on a remote computer, using Remote Desktop Connection)
Regards, Nicolae
"John Winterbottom" <as******@hotma il.com> wrote in message news:2j******** ****@uni-berlin.de...
"Nicolae Fieraru" <no****@please. cxm> wrote in message news:40****** **@duster.adela ide.on.net...
Hi All,
I have a Table1 with ID, Name, Address, Purchase. I want to build a query which shows all the records from Table1 (only
fields
ID, Name, Address), but there shouldn't be listed records which have the same Name and Address (if a customer made two different purchases, he
should
be listed only once). Any help appreciated
without the ID column you can do:
select [Name], Address from Table1 group by [Name], Address if you have more than one purchase for a customer, does it matter which ID is returned? And if it does, what criteria will you use.- i.e. the first, the last etc..
As I mentioned in an earlier response to you regarding slow
queries...build a second query. Make it a totals query. THen link your
current query to the totals query and remove your subquery. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Phil Powell |
last post by:
Here is the scope of what I need to do;
want:
enrollment_year
allowed (even if null)
all of ica
criteria:
|
by: John Fabiani |
last post by:
Hi,
I'm a newbie and I'm attempting to learn howto create a select statement.
When I use
>>> string1='18 Tadlock Place'
>>> cursor.execute("SELECT * FROM mytest where address = %s",string1)
All works as expected. But
>>> numb=10
>>> cursor.execute("SELECT * FROM mytest where clientID = %d",numb)
Traceback (innermost last):
File "<stdin>", line 1, in ?
|
by: jimh |
last post by:
I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.
I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:
(User Table)
UserID
Other user data
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
| |
by: Pavel Stehule |
last post by:
Hello,
Pg make query 1. and 2. very fast (use index), but for query 3. dont use
index. I can solve its using select union, but I readed so pg 7.5 don't
problem with OR operator. I use cvs pg. I used vacuum analyze first.
table sp_op_product has 15K rows, sp_op_uct 37K rows;
regards
Pavel Stehule
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified) problem can avoid these technicalities: the
original table has columns
A1, A2, B1, B2, C1, C2.
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID SalesManName
AT Alan Time
|
by: djflow |
last post by:
Hi!
II was wondering if you can help me with SQL query..
Below 7 separated select query works fine(only when they are retrieved separately)
But I want to combined them together and so that i can make report from my database on the one sheet rather than querying each select statement ( I will use MS Query from the Excel)
=========================================================
1)
select convert(varchar, create_date_time, 112) as Date,...
|
by: Rickster66 |
last post by:
As Instructed this is a new thread regarding my original post: "Select Only 10 Columns Going Back"
I'm sorry for the late response. I've been gathering up information and carefully with as much detail as possible, making clear and straiforward for you.
I need to create 3 new queries based on the queries that you wrote. Each query has a numerical value and a textual value. The new queries are based on the queries with a numerical value. I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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...
|
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...
|
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();...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |