Hi there,
I hope someone can help me with my problem,
I have a database with about on average 30,000 records in, and most of them are duplicates, however the duplicates are required for the work we do, as each represents a different payment, but only 1 action is required.
What I need is a button than you can press and it will go through the entire recordset and will update all the duplicate records and then at the end it will show the amount of unique duplicates updated.
Heres an example of the records in data view:
MPAN | Advisor | Date | Action
--------------------------------------------------------
123456789010 | Bc | 24/09/07 | Cleared rec
123456789010
123456789010
554444444444
554444444444 | TH | 04/09/07 | Unworked
(" | " indicates field)
What I need is for it to start with the first MPAN, update the fields, then move onto the next unique mpan and do the same, and so on.
Thank you,
Brendan
7 2181
Brendan - make sure you have a unique (autonumber?) field in your table;
- create a separate table to hold the unique MPAN numbers (useful for ensuring uniqueness!);
Assuming your tables are called "Main" and "Child", you can now use some code like:- - Update Child Set Child.Action='???', Child.Date=Today() Inner Join Main On Main.MPAN=Child.MPAN
HTH
Steve
Hi, Brendan.
I strongly suggest you to read the article about Database Normalisation and Table structures
And, plz, clarify what do you mean saying
it will go through the entire recordset and will update all the duplicate records
.
Hi,
Basically, I pretty much want something to run through the 30,000 records, identify an MPAN and update its duplicates, then move onto the next MPAN and do the same.
I thought about using more than 1 table to do this, such as a "Child" and "Main" as Steve said, but we have to do new imports everyday and that again causes more duplicates. So one way or another, since we need them, it needs something to deal with duplicates :)
My point still stands. You can still import as usual into the Child table, then check for duplicates in the Main table; if not found, then insert a new Main record, finally running an update query[font=Verdana][size=2], as suggested[/size][/font]
[font=Verdana][size=2]Steve[/size][/font]
I agree with Steve. Some import "difficulties" should not be a reason for breaking database normalization.
Take a look at this thread Mass Import w/Multiple Tables
And you didn't clarified yet what type of update you wish to perform. - do you want to update field(s) with constant value ?
- do you want to update field(s) with matching data from another dataset ?
- do you mean something else ?
I really appreciate your replies, and thank you.
I guess im not really explaining myself that well am i, heheh.
I am currently in the process of converting an excel spreadsheet (which we currently work out off) into an access db, unfortunately the only thing holding me back is the duplicates. Daily imports from an external source means more duplicate records, and updating those records is a required step in the import process, the fields that need updating are the Advisor, Action, and date worked cells.
Creating multiple tables, such as one for all duplicates, and one for non duplicates (main & child) isnt going to be that helpful unless theres a way to update the duplicates first in the morning.
The duplicates occur.. for example if I worked an account yesterday, and it only had 1 record, another payment from that account number might come in on the imports and the same account would then appear again the next day, so any work that was worked previously and has new duplicates imported need to be updated. But since theres so many records, theres no way any person could scan through all those records to manually update them all.
Ideally the thing I am looking for is for it to locate and update the records with the information filled in on a previous duplicate to all those that are blank, and count how many unique mpan's it has updated, and then it would be easy enough to do database normalisation. Before update:
MPAN | Advisor | Action | Date
1234567890 | Brendan | Worked | 24/09/07 <old
1234567890 <new
4321093123 <new
4321093123 | Brendan | Unworked | 23/09/07 <old
2233112233 < new
2233112233 < old After update:
1234567890 | Brendan | Worked | 24/09/07 <old
1234567890 | Brendan | Worked | 24/09/07 <new updated (counted)
4321093123 | Brendan | Unworked | 23/09/07 <new updated (counted)
4321093123 | Brendan | Unworked | 23/09/07 <old
2233112233 < new (not counted, as not worked)
2233112233 < old
Unfortunately I have no idea how to do this.
Thank you,
-Brendan
Hi, Brendan.
Please post the MetaData for your table. Here is an example of how to post table MetaData : Table Name=tblStudent - Field; Type; IndexInfo
-
StudentID; AutoNumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
Mark; Numeric
-
LastAttendance; Date/Time
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paige |
last post by:
I have a database on my local machine that I make entries and
corrections on. I'd like to be able to upload that to my server and have
that update the database that's on the server. What I've been...
|
by: rogsonl |
last post by:
I have built an Access database and seem to have an "id" problem.
When I create records in vb.net and the tables are empty, I get the message,
the id is a duplicate when I update the database.
The...
|
by: beil.jp |
last post by:
Hi All -
I'm new to Google Groups and just a novice user of Access, so please
forgive me if my question is elementary:
I am having a problem using the "update query" for my first time. In
my...
|
by: Roger Withnell |
last post by:
I have a framed website.
I plan to include in default.asp a routine to check if the annual
subscription is due from the records in the People table.
If so, then the routine would send emails...
|
by: Ayo |
last post by:
Hi I'm a newbie here. I'm hoping someone can help me..
Here is the scenario:
I have a bunch of duplicate records I need to clean up
This query below will help me identify the list of duplicate...
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT...
|
by: lotus18 |
last post by:
Hi everyone
I have a little problem in updating a record. I know how add a new record and check if the inputted new data is existing or not as what they have discussed in...
|
by: nomvula |
last post by:
hi guys
i need some help to duplicate records on my form datasheet:
here's the example of my form results:
ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual
UJ...
|
by: xraive |
last post by:
I have a problem with this. Currently I am trying Allen's code and i am not successful.
Current Design
Table1 (Main Form)
TravelID (PK)
ApprovedBY
EntreredBy
BudgetCode
ExpenseCode
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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,...
|
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...
|
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...
|
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...
| |