Trying to get started.

g_king

New member
Local time
Today, 12:11
Joined
Feb 11, 2013
Messages
4
I'm wanting to build a database in Access 2010 to replace a handwritten log sheet that I use for my hobby of metal detecting. I've been keeping these logs since 1994 and the paper is starting to pile up! It would also be nice to run queries/stats on my data. Its basically a tally sheet for common items with a place for notes. I'll attach a copy to this post for reference.

I've watched the Lynda training vids, read some tutorials and followed some links in other forums but I cant seem to get a grip on which sections of my log sheet need to be tables and which fields will belong in those tables.

Can someone please look at the log sheet and tell me which sections need to be a table and which fields need to be related? If I could get started on the right foot I might be able to figure some of this out.

Thanks in advance,
Garren
 

Attachments

Looks like you need just three tables....

The first table would be called something like tblExplorations. It would have the following fields (the things that are only mentioned once per exploration):
- ExplorationID (Primary Key)
- ExplorationDate
- DetectorUsed
- Location
- GPScoordinates
- HoursHunted
- Notes.

The second table would be called tblItemsFound. This is a separate table because you want to record Many items found for One exploration. It will have the fields:
- ItemFoundID
- ExplorationID (foreign key relating to tblExplorations)
- ItemID (e.g. the respective IDs of Penny, Nickel, Toys, Keys, Other etc) (Foreign key relating to tblItems)
- ItemValue (e.g. C, W, Gold Silver etc)
- QuantityFound

You could have a third table which provides the categorisation called tblItems. This table is used for lookup and not for entering your log:
- ItemID
- ItemName (Penny, Nickel etc)
- ItemCategory (Common Coin, Common Items, Jewelry)

Hope that helps.

Chris
 
And the best thing about Stophers suggestion (which is right of course) is that designing a Form would be very straightforward and would look almost exactly like the paperform you're using now. So the database will look and feel real friendly
 
Thanks a million Chris. This helped a ton. I can see now that I wouldn't have been able to figure this out. After seeing your post a lot of what had confused me is starting to make sense now. I can now see how keys relate to my project.

I built the tables per your post but have not built any relationships yet.
I added an attachment field to the first table in case I want to link to photos some day. On the last table, I was wondering, do I delete the first column "ID" that Access automatically puts in or keep it?

Also, I have Access 2010 but when I open the database the top says Access 2007 next to the filename. Does that matter?

I'm finally getting excited about this database. I have wanted this for so long but have been very discouraged. Steve mentioned that the Form should end up looking like my paper sheet. That would be awesome!

I've attached a .zip of the database if you want to look over it so far and grade my work. :)
When I add the relationships. Do I simply link the primary keys to the foreign keys with a 1 to Many relationship?
 

Attachments

On the last table, I was wondering, do I delete the first column "ID" that Access automatically puts in or keep it?
For the Items table you only need one key field so I would suggest you use ItemID and make it Autonumber. Then delete the Access generated key and make ItemID the primary key.


Also, I have Access 2010 but when I open the database the top says Access 2007 next to the filename. Does that matter?
I don't think so.

When I add the relationships. Do I simply link the primary keys to the foreign keys with a 1 to Many relationship?
Yes. Post your d/b again when you've added your relationships.

Good luck with your project.
 
Again Chris, many thanks for your help!

I went back and reviewed some of the tutorials I have on creating relationships and I think I've got them setup properly...not sure though.

I've attached the updated database.

If the relationships are correct, what would be the next step to work on?
My tutorial videos were in the following order...Tables and fields - Rules and Masks - Relationships - Queries - Forms and then Reports. Is this the best order for my project?

Ive only skipped through the Queries tutorials and haven't gotten to the Forms or Reports tutorials which may be why I'm a little hazy on how the third table works.
 

Attachments

Sorry GKing, my software is (I think) too old for .accdb files - I can't open your file to have a look at your DB.

Might you post a screenshot of the Relationships window?
 
Sorry GKing, my software is (I think) too old for .accdb files - I can't open your file to have a look at your DB.
Might you post a screenshot of the Relationships window?

No problem Steve
 

Attachments

  • db_relate_screenshot.jpg
    db_relate_screenshot.jpg
    82.3 KB · Views: 104

Users who are viewing this thread

Back
Top Bottom