I've got a table which lists accounts with an associated charge. If the account has multiple charges, then the account appears multiple times, for each charge. I'd like to consolidate the duplicates without losing each charge that is associated with the account.
Consolidating duplicate values without losing other information
- Thread starter jespen21
- Start date
- Local time
- Today, 15:44
- Joined
- Feb 19, 2002
- Messages
- 47,594
Welcome aboard
It is not possible to consolidate without loosing detail. In a query, you could sum the charges for an account. In a report you can use "hide details" to supress printing of the account number.
Please explain in more detail what you are trying to do.
It is not possible to consolidate without loosing detail. In a query, you could sum the charges for an account. In a report you can use "hide details" to supress printing of the account number.
Please explain in more detail what you are trying to do.
Hello Pat, thanks for your response.
I've got an excel spreadsheet which lists the names, ID numbers, e-mail addresses, room numbers, building name, charge description, amount of charge, and date of charge. For every charge incurred by an individual, all of that information is repeated.
For example, if John is being charged for three different things, his name, ID, e-mail, room number, building, charge description, amount, and date are all listed three times in three different rows.
What I would like to do is have name, ID, e-mail, room, and building all combined so it only appears once, then I'd like the charge description, amount, and date to be associated with that information.
Ideally, the three charges if they are for the same thing would also be numbered, but if this part isn't possible it's not a huge deal.
I've got an excel spreadsheet which lists the names, ID numbers, e-mail addresses, room numbers, building name, charge description, amount of charge, and date of charge. For every charge incurred by an individual, all of that information is repeated.
For example, if John is being charged for three different things, his name, ID, e-mail, room number, building, charge description, amount, and date are all listed three times in three different rows.
What I would like to do is have name, ID, e-mail, room, and building all combined so it only appears once, then I'd like the charge description, amount, and date to be associated with that information.
Ideally, the three charges if they are for the same thing would also be numbered, but if this part isn't possible it's not a huge deal.
You've a few things going on here...
This is standard data normalization. This is good so far. To consolidate, simply create a pivot table of the excel table.
See above. Standard excel pivot table will consolidate name, ID, e-mail, room, building then you'd have 3 listings for the 3 different charges.
This is data duplication. If you're attempting to remove duplicate data, you first need to establish how duplicate data is identified (ie. which columns, or "fields", cause duplication? If a duplicate is identified simply as a row exactly the same as a previous row, it should be a simple task to automatically remove all duplicate rows)...
This is standard data normalization. This is good so far. To consolidate, simply create a pivot table of the excel table.
See above. Standard excel pivot table will consolidate name, ID, e-mail, room, building then you'd have 3 listings for the 3 different charges.
This is data duplication. If you're attempting to remove duplicate data, you first need to establish how duplicate data is identified (ie. which columns, or "fields", cause duplication? If a duplicate is identified simply as a row exactly the same as a previous row, it should be a simple task to automatically remove all duplicate rows)...
- Local time
- Today, 15:44
- Joined
- Feb 19, 2002
- Messages
- 47,594
Relational databases are quite different from Spreadsheets and to create an application that is easy to work with you will need to normalize the spreadsheet data. You will end up with a minimum of two tables probably more. Once you have created the correct schema, you will be able to make forms/subforms and reports/subreports to work with thd data without seeing duplication.
