Advice needed on database design

[Derevon]

Hello everyone,

I was asked by my boss at work who knows I have some experience with Microsoft Access to put together a database tool (I'm using Access 2010 which is new to me, previously I have used Access 2003) to monitor the number of processed invoices for various locations on a daily basis. The idea is that he should be able to have a good overview of backlogs and similar of the various locations.

The idea is that a regular user responsible for each location (or one responsible for several, it's not really important here) should post the number of processed invoices and a team leader let's say should input the number of new invoices received. All this done on a daily basis (workdays only, naturally).

At first it seemed relatively straightforward to me, but the more I think of it, the more complicated it seems.

I want to have one form in which a user can input the number of invoices processed. If an entry already exists for the date/location selected, the old results should pop up in case an error needs to be corrected or similar. Perhaps it would be better if only an admin could do this and from a different form, though.

A similar (near identical) form should exist where it's possible to input the number of incoming invoices for the day.

My boss should have a view where he can see statistics on current backlogs. He should also be able to see some graphs on how the backlog has varied on a monthly basis and such.

So how should I approach this problem? I was thinking of using two tables, one for processed invoices and one for the inflow of new invoices. Or is that a bad idea? I'm not really sure what to use as primary key as no field is unique in itself as each date has multiple locations and each location multiple dates, although a composite key of date and location would be unique. I have never really used a composite key before, so I find it confusing. Or is it better to stick with some kind of auto-incremental number?

More locations will be added with time, so the number of locations will not be constant.

I have never constructed any similar databases before, and the more I think of it, the more confused I get, so I apologise if I omitted some important details here. Any help to get me in the right direction would be greatly appreciated.

Thanks,

Per

 

[CJ_London]

Access 2010 is not that different from 2003 - everything works pretty much the same, you'll just find all the options are in different places

You don't need to use an incremental (autobumber) number but it is better to include one anyway. If a combination of date and location is unique then you can use that as your primary key (in table desing, highlight both fields and click the primary key button)

I was thinking of using two tables, one for processed invoices and one for the inflow of new invoices

Better to have one with an extra field to indicate whether they have been processed or not - or perhaps the processed date (leave blank if not processed)

Think you need several tables, one for locations, one for users and one for process data with a foreign (or family) key in process data relating to locations and a foreign key in locations relating to users.

When designing the field for the tables, bear in mind the requirements for reporting. Alsodon't store subtotals or any values that can be calculated. Avoid spaces and special characters in field and table names and don't use the lookup facility in table design - long term it will cause you problems.

Get your table design/queries working first and then worry about designing your forms

 

[jdraw]

Can you tell us about how your invoices are processed now? For example, is the process automated currently? Seems from what you posted that the boss wants a new system -- implication is that you can't just add some queries/reports to what already exists.
Did the boss (or his/her boss) want you to build a multiuser, multisite invoice processing system?
Need more info.

 

[Derevon]

Thanks for your input jdraw and CJ_London.

I have been given quite free reins on this project. For the moment they're using some kind of Excel sheet for this but are having problems when multiple users are using it simultaneously (probably because the file is on a remote server). Basically, this tool should only keep track of numbers (integers) of invoices added and invoices processed. Actually, not just invoices, also things such as number of e-mail queries and other things, but that doesn't change the approach here I suppose.

As for multiuser, I don't think a full-fledged multiuser login system is necessary here. Anyone can have access to the data entry form. The database doesn't contain any sensitive data or anything, it's just to get a general overview of how the work is going. To quickly see if there are backlogs that need to be dealt with.

Better to have one with an extra field to indicate whether they have been processed or not - or perhaps the processed date (leave blank if not processed)

The thing is that this database will not deal with individual invoices, just numbers, so you can't say that one specific invoice is processed or not. I don't need individual entries for each invoice. One row per location and date that tells us how many invoices were received and processed on this given date should suffice.

Everything should be calculated on a monthly basis (from the first of each month) as reconciliation is done at the ending of a month and the beginning of the next (hence only the current month is interesting). An overview form should display the number of invoices received, processed and the difference between them (this will of course be calculated).

 

[ajparkme]

If you were looking for a comprehensive invoice tracking system, then possibly quite a few tables would be the way to go. However, you seem to be simply talking about raw numbers of invoices (or emails).

I believe you would need one table to store the various office locations (which you would select on opening the database). This would have one field (minimum) which could possibly be the office name, could also be the primary key (assuming there are no duplicate office names) - from a data storage perspective, some people might say that an autonumber field was a good idea here, so that the "office ID" (autonumber) could be used in other tables, but it depends on the amount of transactions as to whether that matters.

The second table could contain three fields -
1) office location (or ID, depending on your choice above)
2) number of invoices (or items)
3) current date (to let you know at least when the invoices were either received or processed)

Received invoices (or items) would have a positive number, and processed would have a negative number. If you wanted to identify which type of items (invoices / emails etc) you could optionally have a fourth field to say "itemType" which in turn could mean you need another table to look up the various item types (e.g. "invoice", "email", "letter" etc.) - again, a design choice for you.

Then you could formulate a reporting query which would have a start date, end date, and would simply add the number of items (invoices etc) together (both positive and negative) to give you your total "on hand". If you had chosen to differentiate between the various items, the query could be built to "group by" the item type, so would give you "invoices on hand" or "emails on hand" at each office location

Is that more what you had in mind?