Web Development



Need Help with MS Access Database.... Noob

By sweiss06 ·
Well, here it goes. I?m fairly new to the world of databases and work has just dumped a Microsoft Access project in my lap. I guess it would be easiest to explain exactly what they want and then what I have so far. . With our company, when you have a job that has an issue we create work request forms. These forms are handed from department to department and make a huge mess and are easily lost in the mix of things. My task as it would be is to create a database that takes all new requests and makes them available to all employees. These are the items needed for this database.

A form in which new work requests can be entered and retrieved from queries. (I would love to be able to hyperlink the job number in each of the queries so on clicking would pull that record on the form) Not having much luck with the coding for that so far.

A productivity report showing the amount of hours each employee has worked on each of the jobs submitted. (When the action to be taken is selected in the combo box I was hoping it could auto load the job hours in a text box that are related to that action, not sure if that is possible either)

3 queries that represent the work requests for our 3 departments, and one query for the management to view all work requests(They want me to sort by date and urgency however I have only been able to sort by date) oldest to newest.

A Log-in Page where managers and users will have separate passwords to give certain privileges. (Managers can view Requests from all departments and view all productivity reports, users can only view requests to their department and their own productivity report; we only have 3 departments for separate requests)

What I have so far:

A form that can retrieve and search the 3 department queries. (Having trouble creating a control that allows me to save newly input records as new rows in the querie, need ability to save over records, and save as new)

The 3 queries for our departments sorting the jobs by date oldest to newest.

A Log-in Form-not functional (Not sure if I should use this or just use MS security functions.

And Finally the Tables that hold the employee info and all the Work requests.

I know this is a lot of info and somewhat vague. But if you can answer even just one of my questions or throw me some ideas it would be greatly appreciated. I?m still getting used to SQL so I?m working my way up to the more advanced coding (FYI)

Thanks again


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I think...

by john.a.wills In reply to Need Help with MS Access ...

that what you need to do first is map out your tables in normal form.
If you want to work requests probably the best solution is to use a report with the sort order set at invocation from a screen input (radio buttons work for me) and the title of the sort order passed to a caption on the report via a public variable.

Collapse -

Too much for a first go

by Tony Hopkinson In reply to Need Help with MS Access ...

Start with something simple and then add to it.

A departments look up, select one and then list job number and description for instance.
Or todays date, assigned to fred....
Get your head round that and then beef it it up.

Don't try to cross all your i's and dot your t's at this point.

Create the Jobs table put fields you know are related to job,
Department ...
Users so you know who did what

Those are the three entities I plucked out of your description (not hard it was a reasonable one).
Then get the relationships clear.
Sometimes the way you describe them can give you a lot of clues how to proceed

For instance Do Departments have jobs or does a job have a department. How do the users think of it.

If you start with the just the demands of the data you want to store, you can end up coding yourself into a very painful corner.
With a system like this and your current knowledge level I wouldn't worry too much about normalisation and look at the task from a functional point of view.
Get something working and then tidy it up.

As far as login mechanism, first you need to think about roles first.
Sysadmin can do anything
View can look at most things, you might want to hide costs for instance, if that's in there.

Now what's in between?

When the roles are clearish, then look at how to implement them, you might end up in bugger it mode if they aren't clear.

Look at some tracking as well, for instance changing a job's state or who it's assigned to or scheduling etc. Perhaps an open system but with a history might be more suitable.

This looks useful, though a few bits may have moved around if you are on a later version. It's got forms and subforms on there which was the hyperlink you wanted.


On a cautionary note how many users and how much data. Have they all got access ? MS have a tool to turn an access application into an exe by the way.

Access is notoriously crap if you have a lot of data or a lot of users changing things at the same time, or you expect to. It tends to corrupt the mdb file, leaving you with total failure or weird data that drops your app on it's ***.

Also various management functions require exclusive access, so you have to tip everybody out do your stuff and then tell them it's back.

Another option you might want to google is an access front end and SQL Server express as the back end. Solves a lot of problems that, though that are a few access specific niceties you won't get, the database inadequacies in access will no longer be a problem. It's much better on the security and privileges front as well.

Collapse -

Access Problem

by a_mcparland In reply to Need Help with MS Access ...

I think the most important thing is to sort out out the tables and the relationships required to produce the queries that are needed once this has been sorted the Forms and reports will become a simple extension of these maybe employee number and work reference number will be your two primary with a one to many relationship ?

Collapse -

thank you so much.

by sweiss06 In reply to Need Help with MS Access ...

I just wanted to thank you guys for all of your input. It's been extremely helpful in the progression of my database. I do realize now that I was getting to in-depth to early and needed to concentrate more on building the foundation before getting into the fine details.Unfortunatley our office only offers Access 97 so i'm also stuck in the software stone age. Again, any other input would be greatly appreciated and i look forward to updating you all on my endeavors into MS access.

Collapse -

MS Access help - Reply

by WSplawn In reply to Need Help with MS Access ...

I live in Sacramento (Roseville) if that helps and am a MS Access Developer.

Email: wsplawn@Surewest.net


Related Discussions

Related Forums