Newbie question about an Access project where forms adjust data on multiple tables

[ianpwilliams]

Hi everyone. I've been trying to put together a project using Excel, and I really think Access is the better way to go. But I have very little knowledge of Access. The idea of the project is this:

There are a group of offices which transfer work between them. One office only transfers work out, and the rest all transfer to each other. There is also a transfer in process in each of the offices (apart from the first one which only transfers out), in which they confirm the number received. So I created the following tables:

TABLES

"Office" Table (which stores the names of the offices)

Unique ID (key)
Office Name

Records:

(offices)

“App Type” Table (which stores all of the App Types)

Unique ID (key)
App Name

Records:

(App types)

"Office Stock" Table (which keeps a record of the stock in each office) (one table for each office)

Unique ID (key)
App Type (taken from "App Type" table)
Number in Stock

"Transfers" Table (which stores records of all transfers between all offices)

Unique ID (key)
Source Office (taken from "Office" table)
Destination Office (taken from "Office" table)
App Type (taken from "App Type" table)
Volume Transferred
Consignment Number
Source Office Comments
Destination Office Comments
Volume Confirmed as Received

FORMS

"Transfer Out" form (one for each office)

The "Transfer Out" form would have these at the top:

Unique ID (key) (invisible)
Destination Office (taken from "Office" table)
Consignment Number
Source Office Comments

Below that, there would be:

Column A - a list of all of the App Types (taken from the "App Type" table)
Column B - the available number of each App Type (taken from the "Office Stock" table for this office)
Column C - the Volume to Transfer field (which must be equal to or less than “App Type Number Available”)

And then a button to confirm all outgoing transfers. This would then create one new record in the "Transfers" table for every App Type which had a number more than zero. It would also reduce the stock levels for those App Types in the current office's "Office Stock" table.

"Transfer In" form (which would take all data from "Transfers" table where the destination office matched this office)

The "Transfer In" form would retrieve all records from the "Transfers" table where the destination office matched this office, and would list them all with the following fields, where only the last two would be editable:

Unique ID (key)
Source Office (taken from "Office" table)
App Type (taken from "App Type" table)
Volume to Transfer
Consignment Number
Source Office Comments
Destination Office Comments (the "Destination Office Comments" and "Volume Received" fields would be the only editable ones)
Volume Received (the " Destination Office Comments" and "Volume Received" fields would be the only editable ones)

When an office confirms a transfer in, they enter a number in "Volume Received", and enter a new comment or overwrite the existing one in "Comments". The volume received then goes into the transfer record, and this office's stock is increased by the volume received.

The user can also enter a "Volume Received" number that is higher or lower than the "Volume to Transfer" number. In this situation, the current office's stock would still be increased by the "Volume Received", but it would also need to adjust the stock of the source office by whatever the difference was. So for example:

Office A has 10 Apps


Office A transfers 5 Apps, leaving 5 in stock
Office B actually receives 7 apps. Office B increases it's stock by 7, and reduces Office A's stock by 2

The user can also enter a different "Volume Received" figure to a figure that is already there (i.e. if they made a mistake earlier). In that situation, the stock levels of both source and destination would be modified accordingly.

I'm guessing I would need VBA functions to do these things, but I've only programmed in VBA for Excel, not for Access. So where would my starting point be?

Also, can I assume that Access would be able to handle multiple users (at different location and also at the same locations) at multiple offices making making multiple transfers at the same time? The idea is that the database would be on a network, which would be accessed nationally.

[Dal Jeanis]

First, the one office that only transfers out doesn't need to be handled any differently, so ignore it. They just won't use the transfer-in form.

Second, check with your manager on the specifications for what to do if the wrong number or items arrive, or if the person does a recount later - I doubt if a GM wants automated systems subtracting from or adding to another office. You might want to have some kind of additional "flag" record that accounts for asking the transfer-out office whether it has X more or less than it's supposed to. You also need to account for what should happen if the transferring office transfers twice in a day, and ships them in the same box. Or for what should happen if two transfers come in from different offices and the receiver switches them around and thinks they are from the other one.

Third, you'll want to split your front end and back end so that the data tables are stored in one place, and the different offices use the individual front ends to make their transfers. There's lots of information on the forum for how to do that. That's how you keep the multiple user thing from corrupting your lunch.

Fourth, I'd never set up a comments system where the next guy could overwrite the comments by the last guy. Better to normalize the tables so that anyone can add a timestamped comment to any particular transfer, as many times as needed. Only a supervisor should be able to update or delete comments, in general, although a worker or admin shoudl be able to update their own comments for a limited time (half hour or hour?).

[ianpwilliams]

Thanks for that. I'll certainly be looking into point 3. The current Excel system would use shared spreadsheets, but I'd rather use Access. And splitting the front ends sounds good.

[ianpwilliams]

Would you happen to have a link about the part about front ends?

A link about how to link a form to a query would be useful too, for retrieving the transfer records for the current office, and then adding or updating data on them

[Dal Jeanis]

Okay, if you're asking about how to link a form to a query, then you need MVP Crystal Long's Access Basics site, which is here - http://www.accessmvp.com/Strive4Peace/. She's posted her entire book for free. The explanations are for Access 2003, but in the big picture, it's the same thing. She also has a basic VBA primer for free on the same site.

Here's Allen Browne's page on splitting databases. http://www.allenbrowne.com/ser-01.html

I'd recommend poking around both of those sites whenever you have the chance. Also, here's a link to a page of links to Access MVP pages. http://www.btabdevelopment.com/ts/de...aspx?PageId=54

Many of them are out of date, but there's a lot of great info that carries straight over into current generation of Access. Mostly the MS Access 2007+ interface and navigation is different, not the theory.

Here's a Microsoft site on splitting. http://office.microsoft.com/en-us/ac...010342026.aspx

Feel free to post any general questions here, today. When you have a more specific question on splitting, or anything else, please start a new thread.

Oh, here's one more great reference site. See the free tutorials on database design and application design in MVP Roger Carlson's site - Roger's Access Library http://www.rogersaccesslibrary.com/ He describes a great technique for analyzing the entities and relationships before you start.

I also wanted to say, your initial writeup was very good, and very nearly complete. Apply Carlson's techniques to what you've got, and maybe you'll find something to fix, or maybe you'll find you have is correct already. Really great start.

[ianpwilliams]

Great, thanks for the advice and links! Looking forward to working on this now!

[Dal Jeanis]

You're welcome. Here's two more good general overviews. Like many of the free resources out there, the examples use the pre-2007 interace.

http://www.learnaccessnow.com/

http://www.functionx.com/access/