Eliminating Duplicates via Queries

[Vylent Fyre]

Hello, everyone! ïŠ I’ve run into another dilemma….

We did a data dump from our system into a csv file and this file is over
120,000 rows. I cannot open it in Excel, obviously. I imported it into a
table inside Access. This table only has two columns – “Account String†and
“Descriptionâ€.

The Account String column looks like –

010-0000-000-000-000000-1100
040-0000-000-000-000000-1100
060-0000-000-000-000000-1100

The Description Column looks like -
CASH
CASH – RIGS
CASH – TRUCKS


This table has over 120,000 rows. I have a query to break the Account
String down to just the Accounts -

010-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€
040-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€

This 1100 account will have up to 20 rows because of the different Divisions
(see how it has 010 and 040?). Also, the description varies depending on the
Division. Note above even though the account #’s are the same, the
descriptions are different somewhat.

I have another query to pull it Distinctly so that I don’t have Duplicate
Accounts - I want to be able to match up the Description to the Accounts. I
am not 100% sure how to best achieve this goal. I'm pasting my SQL for the
queries I've created –


“Chart of Accounts 1st Qry†–
SELECT [Chart of Accounts].[Account String], Left([Account String],3) AS
Div, IIf(Len([Account String])=28,Right([Account String],4),IIf(Len([Account
String])=32,Right([Account String],8),IIf(Len([Account
String])=36,Right([Account String],12),"0"))) AS Account, [Chart of
Accounts].Description, Left([Description],20) AS [Trim Description]
FROM [Chart of Accounts]
WHERE (((IIf(Len([Account String])=28,Right([Account
String],4),IIf(Len([Account String])=32,Right([Account
String],8),IIf(Len([Account String])=36,Right([Account
String],12),"0"))))<>"0"));


“Chart of Accounts 2nd Qry†–
SELECT [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st Qry].Account,
[Chart of Accounts 1st Qry].Description
FROM [Chart of Accounts 1st Qry]
GROUP BY [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st
Qry].Account, [Chart of Accounts 1st Qry].Description;


“Chart of Accounts – Accounts Onlyâ€
SELECT DISTINCT [Chart of Accounts 2nd qry].Account AS [Account Only]
FROM [Chart of Accounts 2nd qry]
GROUP BY [Chart of Accounts 2nd qry].Account, [Chart of Accounts 2nd
qry].Description;

I’ve tried creating a query to bring the Account in the Chart of Accounts –
Accounts Only qry and the Description portion from the Chart of Accounts 2nd
Qry but it brings all of it over (There are multiple Descriptions for one
account.)

What do I need to do or how do I match up the Descriptions to the Accounts
only? I hope I didn’t confuse everyone out there… :|

 

[Vylent Fyre]

Ultimately, I want to have just one row of the account 1100 - CASH
Not 3 rows of the same account number, but different account descriptions.

1100 - CASH
1100 - CASH RIGS
1100 - PETTY CASH

I get the Account # from an expression in a query to eliminate the string in
front of the account #'s. Can your utility do this? If so, how may I use
your utility?

if your ultimate goal is to eliminate duplicates from a text file of 120,000
rows, i developed a utility that does such deduplication.

Hello, everyone! ïŠ I’ve run into another dilemma….

We did a data dump from our system into a csv file and this file is over
120,000 rows. I cannot open it in Excel, obviously. I imported it into a
table inside Access. This table only has two columns – “Account String†and
“Descriptionâ€.

The Account String column looks like –

010-0000-000-000-000000-1100
040-0000-000-000-000000-1100
060-0000-000-000-000000-1100

The Description Column looks like -
CASH
CASH – RIGS
CASH – TRUCKS

This table has over 120,000 rows. I have a query to break the Account
String down to just the Accounts -

010-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€
040-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€

This 1100 account will have up to 20 rows because of the different Divisions
(see how it has 010 and 040?). Also, the description varies depending on the
Division. Note above even though the account #’s are the same, the
descriptions are different somewhat.

I have another query to pull it Distinctly so that I don’t have Duplicate
Accounts - I want to be able to match up the Description to the Accounts. I
am not 100% sure how to best achieve this goal. I'm pasting my SQL for the
queries I've created –

“Chart of Accounts 1st Qry†–
SELECT [Chart of Accounts].[Account String], Left([Account String],3) AS
Div, IIf(Len([Account String])=28,Right([Account String],4),IIf(Len([Account
String])=32,Right([Account String],8),IIf(Len([Account
String])=36,Right([Account String],12),"0"))) AS Account, [Chart of
Accounts].Description, Left([Description],20) AS [Trim Description]
FROM [Chart of Accounts]
WHERE (((IIf(Len([Account String])=28,Right([Account
String],4),IIf(Len([Account String])=32,Right([Account
String],8),IIf(Len([Account String])=36,Right([Account
String],12),"0"))))<>"0"));

“Chart of Accounts 2nd Qry†–
SELECT [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st Qry].Account,
[Chart of Accounts 1st Qry].Description
FROM [Chart of Accounts 1st Qry]
GROUP BY [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st
Qry].Account, [Chart of Accounts 1st Qry].Description;

“Chart of Accounts – Accounts Onlyâ€
SELECT DISTINCT [Chart of Accounts 2nd qry].Account AS [Account Only]
FROM [Chart of Accounts 2nd qry]
GROUP BY [Chart of Accounts 2nd qry].Account, [Chart of Accounts 2nd
qry].Description;

I’ve tried creating a query to bring the Account in the Chart of Accounts –
Accounts Only qry and the Description portion from the Chart of Accounts 2nd
Qry but it brings all of it over (There are multiple Descriptions for one
account.)

What do I need to do or how do I match up the Descriptions to the Accounts
only? I hope I didn’t confuse everyone out there… :|

 

[Vylent Fyre]

I solved my own dilemma - Surprise, surprise!

I took out the GROUP BY for the Description in the query I made - This is
what it looks like for everyone else out there that has this problem -

SELECT [Chart of Accounts - Accounts Only].[Account Only], First([Chart of
Accounts 1st Qry].Description) AS FirstOfDescription
FROM [Chart of Accounts - Accounts Only] INNER JOIN [Chart of Accounts 1st
Qry] ON [Chart of Accounts - Accounts Only].[Account Only] = [Chart of
Accounts 1st Qry].Account
GROUP BY [Chart of Accounts - Accounts Only].[Account Only];


Sorry for this post everyone; I learned something new and I hope this helps
someone else, as well.

VF

Hello, everyone! ïŠ I’ve run into another dilemma….

We did a data dump from our system into a csv file and this file is over
120,000 rows. I cannot open it in Excel, obviously. I imported it into a
table inside Access. This table only has two columns – “Account String†and
“Descriptionâ€.

The Account String column looks like –

010-0000-000-000-000000-1100
040-0000-000-000-000000-1100
060-0000-000-000-000000-1100

The Description Column looks like -
CASH
CASH – RIGS
CASH – TRUCKS


This table has over 120,000 rows. I have a query to break the Account
String down to just the Accounts -

010-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€
040-0000-000-000-000000-1100 will be broken down to 1100 as “Account.â€

This 1100 account will have up to 20 rows because of the different Divisions
(see how it has 010 and 040?). Also, the description varies depending on the
Division. Note above even though the account #’s are the same, the
descriptions are different somewhat.

I have another query to pull it Distinctly so that I don’t have Duplicate
Accounts - I want to be able to match up the Description to the Accounts. I
am not 100% sure how to best achieve this goal. I'm pasting my SQL for the
queries I've created –


“Chart of Accounts 1st Qry†–
SELECT [Chart of Accounts].[Account String], Left([Account String],3) AS
Div, IIf(Len([Account String])=28,Right([Account String],4),IIf(Len([Account
String])=32,Right([Account String],8),IIf(Len([Account
String])=36,Right([Account String],12),"0"))) AS Account, [Chart of
Accounts].Description, Left([Description],20) AS [Trim Description]
FROM [Chart of Accounts]
WHERE (((IIf(Len([Account String])=28,Right([Account
String],4),IIf(Len([Account String])=32,Right([Account
String],8),IIf(Len([Account String])=36,Right([Account
String],12),"0"))))<>"0"));


“Chart of Accounts 2nd Qry†–
SELECT [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st Qry].Account,
[Chart of Accounts 1st Qry].Description
FROM [Chart of Accounts 1st Qry]
GROUP BY [Chart of Accounts 1st Qry].Div, [Chart of Accounts 1st
Qry].Account, [Chart of Accounts 1st Qry].Description;


“Chart of Accounts – Accounts Onlyâ€
SELECT DISTINCT [Chart of Accounts 2nd qry].Account AS [Account Only]
FROM [Chart of Accounts 2nd qry]
GROUP BY [Chart of Accounts 2nd qry].Account, [Chart of Accounts 2nd
qry].Description;

I’ve tried creating a query to bring the Account in the Chart of Accounts –
Accounts Only qry and the Description portion from the Chart of Accounts 2nd
Qry but it brings all of it over (There are multiple Descriptions for one
account.)

What do I need to do or how do I match up the Descriptions to the Accounts
only? I hope I didn’t confuse everyone out there… :|