X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Total Design Assistance Requested, Access 2010    
post Dec 17 2017, 02:24 PM

Posts: 72
Joined: 6-March 07

To All,

If there's a better forum for this post, feel free to move.

I'm looking to design a database in which an end user can enter either a serial number or an asset number, have the info for the item pop up, then enter a problem description along with date. During the course of the resolution, the end user could access a report or something that would tell them where the resolution process is.

I know first table is a no-brainer, only info needed is:

Asset number
Serial number

2nd table would probably have:
Asset number
Problem description
Repair Entity contacted Y/N
Interim status
Date (Using another name)
Device repaired (Y/N)
Device replaced (Y/N)
Issue Completed Date

As a start, that's what I have in mind. I assume Asset number would be related fields, with a 1-to-many relationship. Is there any other issue that may need to be addressed to this point?

After this, I'll try and figure out a form for entering the info, and a way the end user can look at data, but not change it.

Thanks to anyone who is willing to offer suggestions.. My database experience is limited, with beginning Access education taken over a decade ago.

Go to the top of the page
post Dec 17 2017, 03:38 PM

Posts: 1,861
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

My first thought is whether or not a user would know and accurately type a Serial Number or an Asset number (depends on number of characters/digits)
or could choose an Asset from a drop down.

Sounds like you want the user interaction with the data to be read only-- which seems very possible.
Go to the top of the page
post Dec 17 2017, 04:14 PM

UA Admin
Posts: 33,786
Joined: 20-June 02
From: Newcastle, WA

Given the information you indicate you want to track, you'll need at least two or three additional tables to track history for each incident, plus two or more lookup tables.

First, two of the lookups will include:

Interim status in which you list the statuses you want to track for each item being handled. These would be things like, "Original Intake", "Started Repairs", "On Hold", "Complete" and so on, whatever you need to have tracked in this work flow.

You'll probably want a table of standard problems. "Won't start", "Broken Parts", whatever kinds of problems you see.

Now, these in turn will require history, or tracking, tables for the steps in the interim statuses and for the types of problems each item exhibits, assuming that the asset can be presented with more than one problem.

These will both be designed as Junction tables with Foreign Key fields for the asset item and the related "problem description" or "Interim Status" with any additional details you might need for them, such as the date the status was reached.

Finally, you need another lookup and junction table for the details of "Device repaired" or "Device replaced". These are shown as Yes/No fields in your samples, but should be records in another table, not as a repeating group in the main table. I imagine that this lookup will be limited to only a couple of options, but doing it this way allows for better data integrity as well as the possibility of additional outcome options, such as, perhaps "Device abandoned".
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 10:31 AM