DB Design Roadblock - Long Post

[Fozzy9767]

I'm not sure if this the proper forum or even a good subject. I generate reporting every month for a credit card processor. These are Settlement Reports they use to pay the independant sales agents. I am trying to revamp the whole system from the ground up.

My existing process consists of pulling income amounts, expense item counts and costs and a few oddbal items and then posting all this to a report that carries out further calculations, splits the resulting balance and then adds and subtracts a few things that aren't subject to being split. It's quite a mess. I built this as I was learning Access.

I decided to try a bit of a different approach. I built a query that has all my income items and the total amount for each one on a merchant level. (I figure if I get it working there it should be easy to roll up to group totals). I have another query that pulls the majority of my Expense items, counts and costs as well as calculating the totals. I have a separate query that pulls volume totals and transaction counts. My final query just deals with Authorization data, counts, costs and totals.

Then I thought I would make a table called tblitemlist which has the fields itemid(autonumber), Itemcode(a short description), Description and a binary field for income or expense. I have an existing table called sortcodes that has two fields: sortcode(a unique id of each group, it is a foreign key to my main data table so we know where to roll merchant data to), and Name1, which is just the name of the group.

What I want now is to create a table (agentItem)ave fields for sortcode, itemcode(if this group uses this item) and Split(which will be a numerical field for the percent of the item the agent office gets credit for). Some itmes will be at a 60/40 split so I want .6, if they get the whole thing I would put in 1. I will use this in a query that will be the basis for the new reports. It would look like:
Income Item Amount Percent Total
Statement $200.00 60% $120.00
Expense Item Count Buyrate Cost Percent Total
Statement 20 $5.00 $100.00 60% $60.00
Payment
$60.00

This is much simplified using only one item. My main problem now is trying to find a way to build the agentItem table without doing a bunch of hand entry. I may have a flaw in the way I set things up. If so, I'm pretty much starting from scratch so I don't mind redoing some of it. Eventually I will need an interface to add relevant items to new agents as they come on board. Any suggestions are greatly appreciated.

Ken

 

[lespaul]

have you read the fundamentals document linked below? that's a good starting point for identifing entities (tables) and attributes (fields). Having properly designed tables and relationships makes the rest of the design process much easier! Having a field "Name1" indicates that you also have a field "Name2" and maybe "Name3", this indicates further normalization is required.

I suggest you start by reading the document, identify your entities and attributes and then post your table design. Once you've developed that, then you can proceed to user interfaces.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Fozzy9767]

I have read the document, thanks. You are making assumptions about the existence of fields. I have a field Name1 because I recieve a table from an external source with a field "Name", which I consider a no-no. So I add a "1" to it.

I already posted the table structure I am working with. I am trying to keep things normalized.

My main issue now is creating a mechanism, probably form based that with let me do updates to a table with a OTM relationship with my Sortcode table. I'd like to have a form where I choose from a drop-down the agent I want to work with and get a grid containing all items from the tblItemlist plus a check box for whether each item applies to that agent and a drop-down or data entry box where I can input or choose the percentage entry, this would then create an entry in the agentitem table.

I have a bit of trouble conceptualizing how to get from A to B sometimes. That's really the help I am seeking, advice to point me in the right direction. Just writing it out and responding to your post (and it being morning) is starting to help me get a handle on it.

I think I should create my new table, build my form and write my append query to add to the table. Can you see anything I am missing in the design of the agentitem table?

Thanks for your input, Ken.