Hi everyone, i have a database with the information like:
id transId transType
1001 3001 1
1002 3002 1
1003 3002 0
1004 3005 1
1) 1001, 1002, 1003, 1004 is primary key
2) 3001, 3002 , 3003 is the order's invoice no.
3) 1,0 under trans type is their type: 1, is sales, 0 is cancel order
i wish to get this information from database, to display them at report
but i only want to display 1001, and 1004, meanwhile 1002, and 1003 to be excluded since thier transId is same (a customer make an order, and cancel by tat day so i don't want to display "zero result" record)
Can anyone help me to use sql to get a list from database ???which i don't want those 2 records to be displayed?
Many thanks...
4  1817  Delerna 1,134
Recognized Expert Top Contributor
Does this help, it covers the scenario you present -
SELECT Min(ID) as ID,TransID,max(TransType) as TransType
-
FROM YourTable
-
GROUP BY TRANSID
-
HAVING count(ID)=1
-
Dear Delerna,
Wow , the codes works exactly the way i want. However, i am confuse that why shoud we select the Min(ID) ? Can i have your further explanations?
Thank you very much
Delerna 1,134
Recognized Expert Top Contributor
Dear Delerna,
why shoud we select the Min(ID) ? Can i have your further explanations?
Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.
I hope that makes sense
Sure
This is an agregate query and as such every field in the selection must be agregated in some way, otherwise you get an error.
So min is just a way for me to include the ID in the selection without getting that error.
For this particular field in this particular query i could just as well have used max or first or last in order to provide the aggregate function necessary in order to include it in the selection list.
I hope that makes sense
Oops, that's the way it works.. Many thanks again! :p
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: middletree |
last post by:
Yesterday, I posted a question which ended up giving me a lot of info, but
the core question never got answered/solved. The short version is that when
a page displays a number of records based on some criteria that the user
selects, the last record shows up in the source code, but not in the
display. Specifically, I can see 25 records (actually nested tables built
with some loop code) More playing around showed me that adding one more...
|
by: Jack |
last post by:
Hi,
I need to build a asp page where it would serve as a data entry record page
as well as display page for records saved. This page should also allow
editing of records that has been saved.
e.g.
SAVEBUTTON
SS# EntryBox Name EntryBox Date EntryBox Revenue Generated EntryBox
|
by: Corepaul |
last post by:
As input is typed into a text box, I would like to search a recordset for the
first record that matches what has been typed so far. I would like to update
the text box to display the letters typed by the user in the normal font and
any additional characters in the first matching record displayed as highlighted
text. This matches the action of the MS Access help index.
I can use the OnKeyPress event to trigger a new check for the "best"...
|
by: Patrick Fisher |
last post by:
Hi
To display a message when a user attempts to edit a record in a
multi-user environment where a forms Record Locking is set to Edited
Record and another user is editing a record, is difficult because
Access does not trigger an error, I have overcome this problem by
trapping the Delete and Backspace key in the KeyDown event of the form
and displaying a label on the form saying "If Record cannot be Edited
it may be that Another User is...
|
by: MLH |
last post by:
I have a database (datatrek.mdb) with a table named DATA.
The table has a date/time field with default
value = Now(). It has 100 records in it entered over a 50-minute
period.
I would like the query to display 100 records with a new, calculated
field showing timelapse between time of record entry of current record
and time of entry of previous record. For the first record in the
dynaset, I'll settle for a value = 30 seconds. For the...
| | |
by: kimimaro |
last post by:
How do you limits employee display where you enter the department for all
the employee within that department be displayed and if lets say they are
100 or so and you have to limits 10 employee per page before entering
anything to display the next 10 again?
|
by: Bob Sanderson |
last post by:
I am trying to create a form for a MySQL database similar to a spreadsheet.
The idea is to display a list of records, with the last line of the list
being an input form. When the user enters data in the form and hits the
submit button, the data is entered and the form is reloaded with the new
data displayed and another input form becomes the last line.
Example ---
Before entering new data
|
by: Greg |
last post by:
Hi,
I have a table with "dates", i'd like to display those dates on a calendar.
I've put a calendar in a form, linked to my "date" field, and it works, but
only showing one "date" per calendar.
I can see all "dates" moving to next records, but one per calendar. :-(
Ex: 17/06 on one claendar, then if i click on next record, I can see my
calendar with 24/06 ...etc ...
|
by: postman |
last post by:
I have a subform which is bound to a query which will display records using an ID# supplied from a listbox in the parent form.
That same subform is also used to create new records using the following code in a "New Record" commandbutton:
DoCmd.GoToRecord , , acNewRec
Both functions work fine. However, if I have a selected record displayed, and I click the "New Record" button, it does not move to a new record. It just stays on the...
|
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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: 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
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
