Greetings everyone,
I have a problem that I hope has a simple solution. I am using MS Access 2003.
I have a table that is a list of financial transactions. I am using a make table query over that table, and want to return a single record where multiple exist for the five fields that are keys on the table. The rule as to which record I want returned where many exist is to use the record with the most recent transaction date (a field on the transaction table).
I named the five key fields first in the query, and sorted by them. I have used Max(transaction _date) field in the query (and set that to ascending in the sort too), with every other field being set to Group_By, and set the Unique Fields property to Yes in the query properties.
But, I still get duplicate records created in my new table.
One obvious cause that I don't seem to be able to (easily) avoid is that I want more fields extracted in my make table query than just the ones I have named. These fields are things like Payment Amount, so each will likely be different, leading to the Unique Fields property not doing what Iike would like it to do.
One final complication: I only want transactions of a certain type, so I have a criteria set in one of the extra (non-key) fields.
Is there an option in MS Access that I haven't set that I should? Is my expectation that Max will cause MS Access to only retrieve record with the latest transaction date wrong?
Thanks for any suggestions.
2  3554  Rabbit 12,516
Recognized Expert Moderator MVP
Create a query that returns the unduplicated records with only the key fields. And create a second query that will join that query back to the original.
NeoPa 32,569
Recognized Expert Moderator MVP
I can probably help with this but I'm sure I will understand better what you have and what you require if you post a copy of the SQL you are currently using.
That will include the names of all the fields (which I don't know from your post) as well as how you see the data fitting together.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Tony Clarke |
last post by:
Hi,
I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error...
|
by: KENNY L. CHEN |
last post by:
Dear experts,
I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).
Both tables are unique-indexed on (COL1,COL2,REC_NO).
I think the following SQL commands will return the same result but one
of my friends don't think so. He said "QUERY 1" will return 1 unsorted
record (ROWNUM < 2 ) first then sort the result (ORDER BY COL1 ASC,
|
by: Bob |
last post by:
Why, in the process of creating a unique index, does SQL Server allow
me to select the "Ignore duplicate keys" option? Wouldn't I just
create a non-unique index if I wanted to ignore duplicate keys? I
came across this fact while preparing for the SQL Server design exam.
|
by: grist2mill |
last post by:
I have a table 'Group2Operation' that stores many to many relations
between the 'Group' table and the 'Operation' table (each group is has
permission to perform one or more of the available operations)
PROBLEM
=======
I need to prevent duplicate entries being created. e.g. lets say that
in the 'Group2Operation' table a record links the 'editor' group to
the 'publish' operation. Should I prevent an administrator creating a
duplicate of...
|
by: Kragen Sitaker |
last post by:
ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index
We've been getting this error in our application every once in a while
--- typically once an hour to once a day, although it varies over time.
The daemon that gets the error exits and restarts a few seconds later.
Usually it's fine then, but sometimes the error happens three or more
times in a row.
Occasionally, instead, we get "ERROR: Cannot insert a...
| | |
by: David Garamond |
last post by:
postgresql 7.4.0, redhat 7.3 (under vmware 4.0 on win2k)
Windows crashed and some of the files on Redhat got corrupted, including
some files in /var/lib/pgsql/data/pg_xlog/. When I tried to start
postmaster, it fails with message "Invalid primary checkPoint record". I
think it was trying to look for files named "0000000000000021" but only
files named "0000000000000022" through "0000000000000027" were there. So
I ran pg_resetxlog (this is...
|
by: anonieko |
last post by:
> This is a common problem with some solution
>
>
/***********************************************************************************
*
* Problem:
* Determine the Duplicated Records in a table using single SELECT.
*
* We shall be using Northwind database, add some duplicate records.
|
by: mscurto |
last post by:
What is the syntax for an sql command to get the following.
I want to pull in a handful of fields from a table but one of the
fields needs to be unique.
For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?
I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
|
by: Alvin SIU |
last post by:
Hi all,
I have a table in Db2 v8 like this:
Team Name Role
------ -------- ---------------------
A Superman Leader
A Batman Member
A WonderWoman Member
B Alvin Leader
|
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: 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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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.
| | |
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...
| |
