Access Database Project

[Xanadu1115]

Hello all, my name is Taylor and I'm new to this forum. I'm looking for some guidance on a new project I have at work.

I work at a credit collection agency and we have a client that we have to do some account combining for, and due to the high volume of accounts, doing it manually is out of the question. My boss has decided that we're going to use an Access database. Due to the algorithm needed to combine accounts according to their balance, some programming is needed. Since I have no VBA experience, I thought that maybe I could make a custom program in C# that uses the Access database.

I have been successful in connecting to the database and doing basic operations like adding and deleting records. The issue is that since I have little to no experience in database programming, I need some help on how to do this. The accounts need to be run through to see if they can be combined, and then actually combined in a separate table. Here's the basic program flow:

1. The records that are potentially going to be combined need to be brought into the database from a CSV file.

2. Algorithm scans through records:
- If there is more than one account for the same person, see if the
account balance added together is >= $50.
- If >= $50, accounts are combined together into a record in a separate
table for combined accounts.
- New combined account has field that holds account numbers from the accounts that were combined

I'm not an expert programmer, but I would love to be able to come up with a solution for this project and impress my boss. The biggest issue is that I'm not sure how to manipulate data in this manner. If anyone could give me some direction, I would GREATLY appreciate it. This could really earn me some points at work. Thank you so much.

Taylor Hansen

[WingedPanther]

First question: do you know SQL? That is going to be the key to making all of this happen.

[Xanadu1115]

I know the basics of SQL. The rest I need to know, I think I can learn just fine. I've been reading a website all about SQL. I think the biggest issue is learning how exactly to manipulate the data. I've been looking into the DataSet object, because it looks like one of the keys to this whole thing.

[WingedPanther]

(Disclaimer: I know almost nothing about .NET or C#)

I would probably craft a few "clever" SQL statements that could populate almost all the data you have automatically. My biggest concern is that Access is extremely limited compared to other databases (MySQL, FireBird, Oracle, SQL Server, etc). Going with Access is likely to cause you problems later.

[Xanadu1115]

Yeah, Access definitely isn't my first choice, but that's what I'm required to use at this point. If this project goes well we may migrate to SQL Server, which would be nice, but for now I'm stuck with Access. Since it's compatible with SQL statements though, I could get something working. It would probably be a little easier if I knew VBA and could do the coding from within Access' coding environment, but unfortunately C# is only language I know fairly well.

[WingedPanther]

The problem is that Access only allows one join per statement. That can cause serious problems for self-joins, for example.