UtterAccess.com
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    
 
   
orgelizer
post Dec 17 2017, 02:24 PM
Post#1



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
Description
Serial number

2nd table would probably have:
Asset number
Problem description
Date
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.

Ken
Go to the top of the page
 
orange999
post Dec 17 2017, 03:38 PM
Post#2



Posts: 1,724
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.

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Dec 17 2017, 04:14 PM
Post#3


UA Admin
Posts: 31,598
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    19th January 2018 - 02:43 AM