Simple Database (beginner help!)

[Range]

Im trying to build a Database for work but I just don't know where to start!! The concept is pretty simple but making it work seems very difficult-So looking for some guidance please?

Each day in our email we receive 3 different excel files with several hundred rows of accounts that need to be worked. I am looking to merge these files into one DB for multiple users to work from, and mark each row as complete with date, time, name (working the oldest record first). And at the same time create an audit trail as well as a high level completion % report.

• I am not sure if something this simple would need a BE & FE split on the colleague network? (2-7 users max.)
• Also how would I ensure that the multiple users didn't work the same records? ie 7 users logged in and all start at record 1....?
• And Lastly, should I put all the data into one table? Or 3 separate tables? The Excel files that come through are roughly the same layout.

Thanks for any help, i'm on Access 2003 running on XP.

 

[Range]

Also would there be anyway that on the form I could have my high level reporting??? Instead of on a report so I can have everything in one place....

I would want this by Date. So say if all the accounts from one of the files has 100 records spread over 5 dates. I'd want to see how many worked out of 100. Something like;

5worked / 100accounts
01/06/11 21accounts
02/06/11 19accounts
03/06/11 15accounts
04/06/11 20accounts
05/06/11 20accounts

Thanks for any help....

 

[Hermanito]

First thought: don't combine reporting with data entry/data handling, it will be very confusing...

If the three Excelfiles are of a similar layout, it would indeed point to gather all data in one single table. You could include an extra column to indicate from which type of sourcefile it came, in case you would ever need to trace something...

Will you and/or your colleagues be working on this data directly in Access? In that case a BE/FE split might be overkill, although, if you want to avoid two people working on the same record at the same time, you will need to create some level of split: at the very least forms within Access where you can only work on one record at a time, and while one person has it open, you mark the record as work-in-progress. When someone else opens the form and requests a new workitem, the query should exclude the work-in-progress records... something like that. The forms would be your FE then, and the tables the BE.

 

[Range]

Thanks for the steer Hermanito!!

If the three Excelfiles are of a similar layout, it would indeed point to gather all data in one single table. You could include an extra column to indicate from which type of sourcefile it came, in case you would ever need to trace something...

On the form would it be possible to only work one of the three sourcefiles? As this is what we commonly do at the moment, ie through as many hours as possible at each sourcefile at a time.

Will you and/or your colleagues be working on this data directly in Access? In that case a BE/FE split might be overkill, although, if you want to avoid two people working on the same record at the same time, you will need to create some level of split: at the very least forms within Access where you can only work on one record at a time, and while one person has it open, you mark the record as work-in-progress. When someone else opens the form and requests a new workitem, the query should exclude the work-in-progress records... something like that. The forms would be your FE then, and the tables the BE.

Ahh ok that make sense. We're only using the DB as like a dairy to organize the work better, the actual work will be done in separate software application for the accounts-however name and date worked will be added in Access.

Would it be hard to program the form as you mention - not sure if my skills will stretch that far!!!

 

[Hermanito]

I see, so the thing in Access would be more of a work distribution and tracking tool, than for doing actual work, correct?

On the form would it be possible to only work one of the three sourcefiles? As this is what we commonly do at the moment, ie through as many hours as possible at each sourcefile at a time.

What do you mean by that? You get three Excelfiles containing similar data, but you work through them sequentially rather than concurrently?

When you have the extra column in your table that indicates for each record from which file it came, it would be simple to filter on that field when selecting or showing workitems...

If it's hard to program is difficult to answer... I'm fairly sure that a good Access programmer should have a working prototype in a day or less. Depending on the amount of finetuning, customisations, etcetera... that could easily become a week or more...

I'm not very experienced in pure Access development myself, but I'm willing to assist where I can... seems like a nice challenge that can teach me a few things as well