Of Tabs and Checkboxes (1 Viewer)

[kurtis51]

Let me just start off by saying, I don't have any code to post as it is just an idea I have.

This is a Lease return project for work. We have an excel sheet that is broken up into tabs. Master List, Not Returned and then a tab for every month.

Here is what I would like to do.
If someone checks the box that says the leased computer was not returned, can I grab a certain batch of fields to automatically populate another Tab in this Access 2003 Form?

The second part to this would be if a person unchecks "Not Returned" in the Not Returned Tab, can I get that record automatically removed from that list view?

Similar principle for the Monthly shipping by a certain date. If the user enters in a shipped Date by say March 1, is there any way to populate a another tab into a list view?

Thanks for any advice that you gentlemen and ladies can provide.

 

[Alisa]

I think your question is confusing: First you said

We have an excel sheet that is broken up into tabs

then you said

automatically populate another Tab in this Access 2003 Form

Are you in Excel, or Access, or both?
I think you might get some responses if you are a little more clear - do you have an existing database in Access?

 

[kurtis51]

RE: your question

Sorry let me make this clear.
We have an excel spreed sheet that we are using right now. My boss wants to go to an Access database because with a form it will be easier to input data and everything will be formatted.

I have started building an Access Database and in the form I was using the Tab object that comes with Access Toolbox.

I hope that answers your question. Also, to let you know because the Excel file is shared and I cannot import it into Access.

 

[kurtis51]

Just think of it as creating a Database from scratch.

 

[Alisa]

It sounds like you may have the cart in front of the horse. You need to design your data structure first (it will not look anything like what you have in excel). I don't have much of an idea of what you are trying to do, but from your first post, it sounds like computers would be a main entity in your database, meaning you need a table called computers and each record (row) in your table is one computer. Then you could have fields (columns) in your table for date returned, etc. There are lots of posts on database design and normalization in this forum. You should leave form design alone until you've got your data structure figured out. HTH

 

[kurtis51]

Thanks, I've just started to recreate the Database. Originally I had this:

Table: Technician
Location
Tech. Name
Tech EID
Comments -about old computer

Table: Lease Refresh
End of Lease Date * Shipping: Table same field
Refresh Email Notification Sent
Refresh Confirmation Received
Dell E-Quote
Date Approval Sent
Date Approval Received
Online Purchased Requisition #
Scheduled Refresh Date
Obtain List of Applications Required
Confirmation of Delivery Date
Dell Order Confirmation (Checkbox)
Lease Schedule # (preset number)

Table: User Data
Old Asset Tag #
Model - Separate TABLE: Model (eg. D600, GX280)
Type - Separate TABLE: Type (holds Laptop, Desktop, Workstation)
Last Name
First Name
Employee ID
SBU - Department
Location ID
New Asset Tag #
New Asset Delivered

Table: Shipping
*Lease End Date
Pickup / Shipping Date
Pickup (P) Shipped (S)
RA Number
Waybill Number (Purolator)
Wipe Date
Certificate Number
Tech EID

Table: Paperwork to boss
Refresh Docs Sent (Date)
Master Inventory Updated Sent (Date)
Monthly Lease Updated (Date)
Old Computer Not Returned (Checkbox)
- Old Computer Stolen (Checkbox)
- Old Computer Purchased (Checkbox)

------------------------

Now I have combined the Shipping, Lease Refresh and User Data into one Table. Technician, Model and Type are still separate tables.

*Note*
If "Not Returned" is checked All Fields from User Data Table (minus New Asset Tag and New Asset Delivered); Technician: Comments; Paperwork to Boss: Not Returned; Shipping: Pickup / Shipping Date.
- This all MUST go into another Form in a Grid View. Once the computer has been shipped, (eg. Shipping Date changed)
- Record must be deleted and Month that it was Shipped on must be updated in it's respective form.

-------------------

What are your thoughts?

 

[gemma-the-husky]

you need to think of everything in terms of the data, ratgher than specifically trying to move "a bunch of fields " as you put it. simply design queries that provide the information you want, rather than trying ot manipulate specific pieces of data - its a different way of thinking

you probably have the following (i assume you are leasing equipment to third parties)

a) equipment details (refno, description, serialno, etc)

b) rental contracts (equipment ref, contract start, contract end, returned flag)

etc

now, when you have a contract, you set up the details in table b, which links to the item in table a (you therefore can have several rental contracts for each item over a period of time)

now you have a flag in table b to indicate whether it was returned (in fact thiswould work better as a date returned, as it conveys the same information with more details)

given this structure you can use the dbs to extract information such as

a) all rental contracts expiring between a range of dates
b) (what you want) all rental contracts where the equipment came back late, or is still outstanding

the presentation of this will be up to you, but it flows from the data structure and design - you dont want to think in terms of moving a number of fields - all you should need to do is set the data appropriately, and requery the data, which will automatically refresh the presentation as appropriate.

 

[Alisa]

I think you are heading towards the right track, if not quite on it yet Have you done any searches on database normalization yet? There is quite a bit of good info out there. For starters, each one of your tables should represent one and only one subject. So with techs I think you are probably pretty close. But with the other tables I think you have some work to do. You probably need one table for computers that would link some of the other entities together. For instance, the computers table could have a techid to say which tech worked on it, and a userid to say who it belongs to. Obviously I don't understand your business situation, but you get the idea. Also, your paperwork to boss table is probably not necessary. For the date docs sent, what are those documents related to? That date should be stored with the entity that it describes. Same goes for inventory, etc. dates. I think you need to put aside any thoughts about when records will get deleted, etc. and just focus on getting a good data structure. I know you are probably under pressure to throw something together, but if you don't take the time to design it well now, it will not be usable in the future.

 

[Rabbie]

As a general rule Access tables should be tall and thin rather than short and fat. Better to have a lot of relatively short records tah a a few long ones. You do need to be sure you understand Normalisation before you fix your design. If you don't you will have ongoing problems every time things change.

 

[kurtis51]

Well I've made some strides today. I actually had a good brainstorming session with my boss and another guy that's a mentor of sorts. Unfortunately, all the information I wrote down was lost because my computer decided to restart on me... ya fun.

I did more work on the DB today. In fact, here's a picture of my Relationship Table, rather than me typing it all out.

Again, let me know if I'm on the right track.

So, to answer your questions:
Alisa:
I have read a bit about DB Normalization, confusing as all heck. I understand that you need to make the DB as simple as possible as well as functional. I'll try and find a site that is a bit easier to understand.

I will layout the Process of how we do things, maybe it will give you a better picture of what I am doing (I probably should of done that from the start.)

- Look on an Lease Refresh (Refresh) excel spreadsheet and see which computers are coming due (Lease Return Date)
- Email the User and verify that, that system is indeed his/her's and is to be Refreshed.
- Verify that the system is either to be Purchased, Stolen or to be returned.
- Receive email from user saying that, it is his/her system that will be Refreshed.
- create a Dell Quote and fill out an EApprop form. Record EQuote Number
- Email EApprop form off to him/her, their boss, their bosses boss and their Financial lead
- Receive approval for said computer for Refresh
- order said computer through iBUY record Confirm number
- Confirm Delivery date of computer to your tech dept.
- Receive said computer and set it up. (Record Asset Tag information)
- Contact User and confirm a Deployment Date (record Date)
- Contact User and confirm Apps User needs (record Date that was done)
- Setup computer and bring back Old Asset.
- Wipe Old Asset (Record Date of Wipe, Wipe Certificate and your Tech ID)
- prepare for shipping, use whatever Shipping method you have (Shipper is a 3 digit char for say UPS)
- Record the RMA and Waybill numbers for the shipping
- Say whether it was shipped or picked up (Record Date)
- Email Boss with Refresh Data, Wipe Data, Shipping Data, and you updated your Master Inventory List.
- If the system was not returned or shipped due to stolen or purchased that must be recorded in the Comments section.
- Also If it was not returned the reason in the comments is not enough. It was either Tech's fault (for whatever reason) or the company itself didn't want to ship it (due to purchase or because they couldn't due to being stolen).
-------------

That's the process that we have to deal with. So to answer your question about why is the paperwork necessary, redundancy. To answer your other question, 'Am I being pressured to create a DB quickly' Not the case. While I would like to get one going, my main focus is to get Data entry and saving records so that I can test out the DB and present a working copy by next month. This also goes into the fact that I need to clean up / organize the Tables that I do have.

The other issue is that 100% of the company uses Excel. It's not going to be easy to change the companies mind unless there is a working copy So, yes, I have some work ahead of me. But if I can organize it better, the rest will come with help and time

 

[Alisa]

Now you're getting somewhere. I agree there is a lot of confusing stuff out there on the web. If you have some time, you might want to get an introductory book on database design because that will really lay out the whole process for you. Just to give you an example, Database Design for Mere Mortals is really simple and to the point. None of us just looking at your design will be able to tell you for sure that it will work, because you are the only one that really knows your business process. That said, one thing I noticed is that in your lease table, you have fields for not returned, purchased, and stolen. Those are all three sides of the same coin, no? So you could have one field called status, and then store "NR" for returned, "P" for purchased ,"S" for stolen, and "UN" for unknown. That way, when you want to create a report later, you can group by status (put all stolen in one list and all purchased in another).

 

[kurtis51]

Actually I got the Dummies book 9 in 1 and it's helping a bunch. I haven't DB / VB programming in about six or seven years really since College. I'll see if I can find that book online as well, Thanks

As for the business process, I've laid it all out there. It's more time consuming than anything, of course doing things right through these steps makes things albeit easier. It's one of those, you have to do it to understand kind of process.

For the Lease Table and specifically Not Returned, Stolen, Purchased. In talking with my boss about it, Stolen and Purchased are just tossed into the Comments section, so I really don't need them as checkboxes then...

However, the Not Returned will have to enable a list box for either Tech or Business. because it was either the tech's decision not to return it (due to missing a shipping deadline) or the Business (for purchasing it or for it getting stolen)

I do like your idea for Status though, I may have to pass that by them see what they think. I would have to change the lettering though as I have "P" and "S" used for Pickup and Shipped. Maybe I could use: "PU" for Purchase "ST" Stolen and "NR" for Not Returned. If it is "UN" for Unknown we have more issues than a computer not being accounted for... hence the emailing the user before hand.

 

[Alisa]

Sounds like you are on your way - good luck. Just one warning for you, no matter how convincing it sounds, "thats the way we do it now" is NEVER a good basis for database design. You need to think further down the road. For instance, you said,

Stolen and Purchased are just tossed into the Comments section, so I really don't need them as checkboxes then

You might want to rethink that. If you throw it in with the comments, murphy's law says its only a matter of time until your boss thinks, now that we have this great database, why don't we make a report of all the purchased leases. But you will be out of luck because you didn't store that data separately . . .

 

[kurtis51]

I found that Database for Mere Mortals that you suggested Alisa. It's a good book I must admit, thanks

Problem is, now I'm looking at my Tables as shown above and I'm thinking that the tables just aren't fitting right. There seems like there is almost too much data in the Refresh Table...

I'll post a new picture of the Relationship I have so far. Also, I'm creating forms for all of my tables so I can enter in the Data I need... apparently that was the first issue with my form in not being able to create records. I ended up looking at another database for their structure and it helped that way, but I want to have a solid database design like you guys are talking about first before I start to populate this DB.

I was looking at the Process guide that we have and I was going to actually place the tables into their respective sections such as: Tables named; Identification, Quote Creation, Approval, Purchasing, Receiving, Deployment and Returning. Then have one main table linking all of the ID's together called LeaseReturn.

Any thoughts? Or is this a bad design flaw in Relationship DBs?

 

[Alisa]

I'm not sure about the design you are proposing, but they best way to find out if it is good or not is trial and error - just keep asking yourself what would happen in different scenarios and you will figure it out eventually.

 

[kurtis51]

Trial and Error is good, however...

I'm not sure about the design you are proposing, but they best way to find out if it is good or not is trial and error - just keep asking yourself what would happen in different scenarios and you will figure it out eventually.

I ended up redoing my tables. I think that I got it right this time around.

Anyone's thoughts as per usual if you please.

How am I going to get the main form to recognize that the ShipperID, TechID, ComputerID and StatusID fields in the Workorders Table are actual fields with data in them rather than just a number?

Is there anyway to be able to just enter in the Data as if I had a form like the second attachment?

 

[Rabbie]

I ended up redoing my tables. I think that I got it right this time around.

Anyone's thoughts as per usual if you please.

How am I going to get the main form to recognize that the ShipperID, TechID, ComputerID and StatusID fields in the Workorders Table are actual fields with data in them rather than just a number?

Is there anyway to be able to just enter in the Data as if I had a form like the second attachment?

Look at the Northwind sample DB that ships with Access. This will show you how to do what you want.

 

[kurtis51]

I appreciate the help Rabbie and while that does help a bit, I'm looking more for the confirmation:
1) my relationships are good
2) that I can in fact take say the EmployeeID field in my Form and split that into three separate fields on the Form, one for Last Name, First name and their Employee Number.

 

[Rabbie]

See the string functions for help on how you can split the EmployeeID field. Left Mid and Right should let you split it any which way.

Your relationships seem OK but only you really know the business model.

 

[kurtis51]

Unfortunately, I need all the fields in each table. I mean the only reason why I created multiple tables in the first place was because logically they fit into those tables and it would make grabbing data from them easier in the long term.

Plus, if I wanted just one long table I would of stayed with Excel. The Form itself will be alot easier than Excel because everything is on one screen.