Looking for a lot of help

[PsyscopeHero]

I have the inspiration to do something really cool for my workplace, and I think I would need to work in access, but I have never even seen a database before. I am pretty computer savvy, and I learn fast, but I just have never had a reason before now.

What I would like to do is have names linked to some other information, like a number or another item that we use for identification. I am sure this is a simple operation.

For another project, I would like to have it so each input could have two possible outputs, and they are always chosen sequentially.

For example, lets say I put 1 in cell A1 in excel. In B1 I would like "A" to appear. If I type 1 in A2, I would would "B" to appear in B2.
If I type 1 in A3, I would like NOTHING to appear in B3.

It is also important that I be able to put A and be "back".

so lets say that I go back and type "returned" in C1... now if I type 1 in A4, I want B4 to have A appear.

I hope that makes sense. I just need enough guidence to get me in the right direction... then I can figure it out from there.

Thank you!

Chris

 

[qdogfball]

Unfortunately, you are really going to have to think about what it is that you want!

If you want a simple database that has employee names linked up to an employee number, that does not require a database.

But, if you have an employee attendance issue, that would be nice to have a database that houses their attendance and then you could get reports on who was here, who took vacation, who was sick, etc.

Do you follow?

Not sure about your second case.

There are times in a database that IF a certain thing happens, say invoices whose amounts got over $10,000.00, you want a report to show you every company that ordered that much this past week.

There are also time when if in the above example of employee attendance, a person is sick, another field would pop up (from being invisible) and ask "why are they sick?" and then you could answer from a list of reasons or just type in things.

Hope this helps you gather your thoughts!

 

[The_Doc_Man]

Before you ask us for anything (unless it is gross concepts), you need to firm up your design goals a bit more.

 

[PsyscopeHero]

Firming up my design goals.

Alright, I admit, I really did not know how to state my goals before. This is what I want.

Right now, we are working with excel, and I still want excel to be the interface for logging information.

The best way to explain the rest I think would be with a even more basic example, it is how I have been explaining it to others I have asked for advice...

Lets suppose I am working at a library where everyone can only have two books at any given time. If Steve comes in and checks out two books, he is not going to be able to check out any more until he returns at least one of them. Of course, he is not the only patron. At present, everyone has two cards that I keep behind the ciculation desk, and I stick the card in the book when they leave, and I take it out and replace it when they bring the book back, and this way I know how many books they have out no matter how much time has passed, but using cards is slow and leaves room for error.

How can I have a computer keep track of his outstanding books at any given time? As well as Steve's name, I also would like to log the Title of the book being borrowed, the date and time borrowed and returned.

Finally, I would like to log how many books are returned damaged. For every book returned damaged, Steve looses the right to check out one book, so after two times he cannot check out any books, unless I go into the system and clean his record.

This is exactly what I am trying to do. All of this must occur in excel, and I would like the number of books outstanding to be tracked automatically, and the system to refuse or give some sort of alert is a 3rd book is requested.

This is very long winded, but I dont have the technical vocabulary to put it any other way and make it clear. I hope this helps in you helping me. Thank you all!

 

[PsyscopeHero]

In responce to qdogfball: As it is right now, everyone is granted two unique numbered cards. Upon their first request, one is given, and on the second, the other unique card is given. The same goes for the entire population involved. I would like to eliminate the cards and have this tracked similarly on the computer, but the computer has to be able to recognize names in relation to these two unique passes, and be able to track if these passes are used or returned, and not alow any more than two to be used at a time... again, it needs to be this way for 1000+ individuals, all with a unique pair of cards that operate independently of all the other people. I know I am over complicating this, but that is what I meant.

 

[neileg]

What you have outlined is perfectly possible in Access and shouldn't be too challenging. When you start out with a design you need to think about the entities involved. As a rule of thumb, you need a table for each entity. I can see 3 entities, people, books and loans. So you need a table to deal with people data, a table to deal with book data and a table to deal with the loans.

Since loans relate to people and books, your loans table will hold the unique identifier for the person and the unique identifier of the book in each record. Your tables might look like this:

tblPeople
PersonID primary key, autonumber
LastName
FirstName
etc.

tblBook
BookID primary key, autonumber
BookTitle
etc

tblLoans
LoanID primary key, autonumber
PersonID foreign key link to tblPeople long integer
BookID foreign key link to tblBook long integer
LoanStartDate
LoanEndDate
LoanType (normal loan, card suspended etc)

When you go to issue a book, you can test to see the number of loan records that person has where the LoanEndDate is null. This tells you how many current loans there are. If the count is 2 or more, they can't take another book.

Should get you started.

 

[PsyscopeHero]

Should get you started.

Thanks a lot Neil! I did get a start and I learned how to create a relationship! I am going to play around some more with this and see what I can learn, but in the meantime, for my primary goal, I want data to be logged and limitations on books checked out via excel... is that possible? I have the impression that it is.

 

[neileg]

Two things.
1) If you don't get the design right, nothing will work properly. If your foundations are wrong, the house will fall down.
2) What I have described will give you the data and the basis of controlling loans in an Access application. This remains the case if you want to use Excel as an interface (though I certainly wouldn't want to do this).

 

[PsyscopeHero]

Two things.
1) If you don't get the design right, nothing will work properly. If your foundations are wrong, the house will fall down.
2) What I have described will give you the data and the basis of controlling loans in an Access application. This remains the case if you want to use Excel as an interface (though I certainly wouldn't want to do this).

I appreciate this, and the impression I am getting is that if you want something to work well, you need to really know what you are doing... Access is not something you can learn on the fly like Excel.

This may be the case, and maybe I need to spend some more time learning the program and TRULY understanding what it is I want done and what it is that I want the program to do (because while those are kind of the same, if you dont know the right questions, you wont get the right answers). Thank you all!

 

[neileg]

I appreciate this, and the impression I am getting is that if you want something to work well, you need to really know what you are doing... Access is not something you can learn on the fly like Excel.

You can learn on the fly. Trouble is that most users have cut their teeth on Excel (or similar) and don't just want to do simple things. Most novice Excel users won't be using Vlookups, for instance - adding a1 to b1 and getting the answer in c1 will do nicely, thank you! But they expect more from their first steps in Access.

This may be the case, and maybe I need to spend some more time learning the program and TRULY understanding what it is I want done and what it is that I want the program to do (because while those are kind of the same, if you dont know the right questions, you wont get the right answers). Thank you all!

Dead right. There's advice on these forums about db design that is worth its weight in gold (if electronic advice has a weight...). If you can't figure out what you want on paper, Access isn't going to make it any easier.

Good luck. Everything I know about Access, I learned on here.

 

[PsyscopeHero]

Good luck. Everything I know about Access, I learned on here.

Where should I start looking on this site to learn more? Start doing tutorials? Thank you!

 

[neileg]

Up to you. My learning has largely been task orientated. I hardly do any programming these days, just lurk here to 'keep my hand in'.