Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-14-2019, 08:34 PM   #1
Canoehead
Newly Registered User
 
Join Date: Mar 2019
Location: Ontario Canada
Posts: 4
Thanks: 4
Thanked 0 Times in 0 Posts
Canoehead is on a distinguished road
Noobieissues

I am teaching myself access and find I donít have some of the vocabulary to explain what my issue is. Known unknowns. Anyway I made a dB with a couple of tables in it. I would like to have my table keep every record entered with a date time... then when making a report, it would display the most recent iteration.

My tables etc deal with a high rise appartment. Number of units, up to three parking spots. Storage locker etc. assigned to units

Ideally I could then search back in time to see the state of play on any given date.

1) Does this approach seem sensible?
2) if so, how do I get my table to do that? Atm a modification to the table via the form simply overwrites - updates the record.

Access 2016
Thanks in advance 🇨🇦

Canoehead is offline   Reply With Quote
Old 03-14-2019, 08:46 PM   #2
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 716
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Noobieissues

I doubt a "couple" of tables will cut it - at least not if properly normalized.
Quote:
I would like to have my table keep every record entered with a date time
What about edits, or are you not interested in that? Seems to me a minimum of 3 tables without worrying about tenants or history. To get a focused answer would require much more information about the process; i.e. what entities you need to deal with, what it is you want to track historically (could be tenants in a unit and even moving locker assignments, etc).
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Canoehead (03-15-2019)
Old 03-15-2019, 05:55 AM   #3
Canoehead
Newly Registered User
 
Join Date: Mar 2019
Location: Ontario Canada
Posts: 4
Thanks: 4
Thanked 0 Times in 0 Posts
Canoehead is on a distinguished road
Re: Noobieissues

thanks for responding

My fiance manages a 232 unit high rise. All of the units are individually owned, however some of them are in a rental pool managed by her as well. some of the units are rented out to tenants directly by the owner.

things she now keeps track of in excel.

Plumbing inspection of units. (toilet flex supply line inspection, water heater inspection, water softener inspection, etc)
Parking slots assigned to each unit
A limited amount of lockers assigned to units (not every unit gets a locker)
Owners contact information

Parking slots will change over time as will lockers and owners information.

I would like to be able to keep track of all of this data by date so that, if she is asked she can go back to a date in time and recover the information. Such as who owned unit 232 last year...or what parking spot(s) was assigned to what unit at a point in history.
I guess i cant post images yet. is there a way I can show you what i have?

any help is greatly appreciated

Canoehead is offline   Reply With Quote
Old 03-15-2019, 06:09 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,307
Thanks: 153
Thanked 1,690 Times in 1,662 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Noobieissues

You sound as if you need a Unit "header table" and then at it's simplest an event / entity table, and an event type table.

So your tblUnkits would store the top level info the unit number, possibly a location, things that don't change , or at least if they do you aren't worried about when.

Your EventType table would hold all the event types you want to record.
Then you would have your Events table, it would have the UnitID as a FK, the EventTypeID as a FK , EventDate as a field, and possibly a PersonID as who made or is responsible for the change. You could even add a EventNotes field for simple comments associated with that event.

This is a very simple view of what could be come quite complex if you were trying to map out parking spaces, lockers, etc, the model can become more complex, but based around a similar design.

So if one of your events was Boiler Service (Lets say EventTypeID = 23) It would be simple to query your events table to show the last time a unit had that event, or even had never had that event.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Canoehead (03-15-2019)
Old 03-15-2019, 06:47 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,936
Thanks: 79
Thanked 1,566 Times in 1,454 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Noobieissues

You are trying to design something. Time for me to trot out my design advice.

Old Programmer Rules #1: If you can't do it on paper, you can't do it in Access.

That means you must first know what you intended to do. You need to define your problem and then build a roadmap that will get you there. The idea is that Access is just a tool. You are the craftsperson. You must decide what you want to do and that requires problem analysis. In this phase, you will have to decide what data you need to keep and how you need to keep it. Once you know what you want to do, you can then proceed with issues in implementation.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

You want certain things to come out of Access. That means you have to have a way to get those things INTO Access in the first place. Whatever you want out, you must have a way to input that, to store it, and to work with it if it involves computations. If you want X, Y, and Z, you must have inputs of X, Y, and Z. If you wanted XYZ, you need to input X, Y, and Z AND you must include the formula to transform them from single to computed or combined entities. This often means looking at each desired output and working backwards through your processes to see where you get the data to support the output item in question.

As noted by others, you need to understand the concept of "normalization" because coming from an Excel environment, you are most likely NOT going to be working with fully normalized data. You also claim to not know how to ask the question and I fully understand that. Happens all the time and gets solved by experience and by browsing this forum.

Search advice: If you search this forum, you want articles on "normalization." IF you search the web, use "database normalization" because in a general context, you might get political, social, chemical, or other uses of the word "normalized." Further, if you do a full web search, you might want to initially limit yourself to college campus articles on database normalization before stepping into the more commercially oriented web sites.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Canoehead (03-15-2019)
Old 03-15-2019, 07:42 AM   #6
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 396
Thanks: 21
Thanked 87 Times in 86 Posts
mike60smart will become famous soon enough
Re: Noobieissues

Hi

Can you upload a zipped copy of your Db so we can see what table structures you currently have?
mike60smart is offline   Reply With Quote
Old 03-15-2019, 07:42 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,938
Thanks: 74
Thanked 1,969 Times in 1,917 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Noobieissues

Further to the great advice you have been given so far, I'm suggesting you work through 1 or 2 of the tutorials from RogersAccessLibrary in this link to articles on Database Planning an Design.
Once you experience the process by working through the tutorials, you will be able to apply what you have learned to any database.
Start with an overview of the proposed business - at the 30,000 ft level. Then gradually add detail. You will find from the tutorials that a clear description of the "business" is key to understanding the business rules and the associated tables and relationships. Create some test data and test scenarios and test and refine your data model. Get a tested design on paper before jumping into physical database.
Good luck with your project.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
Canoehead (03-15-2019)
Old 03-15-2019, 08:44 AM   #8
Canoehead
Newly Registered User
 
Join Date: Mar 2019
Location: Ontario Canada
Posts: 4
Thanks: 4
Thanked 0 Times in 0 Posts
Canoehead is on a distinguished road
Re: Noobieissues

here is a copy of the db structure i have so far. remember its my first stab

(the usual here)drive.google.com/open?id=1EUwCWzDo-0LcAyd8zH1OM-h1iu0s9Q_Y

Last edited by Canoehead; 03-15-2019 at 08:51 AM.
Canoehead is offline   Reply With Quote
Old 03-15-2019, 10:15 AM   #9
Canoehead
Newly Registered User
 
Join Date: Mar 2019
Location: Ontario Canada
Posts: 4
Thanks: 4
Thanked 0 Times in 0 Posts
Canoehead is on a distinguished road
Re: Noobieissues

Once you experience the process by working through the tutorials, you will be able to apply what you have learned to any database.
Start with an overview of the proposed business - at the 30,000 ft level. Then gradually add detail. You will find from the tutorials that a clear description of the "business" is key to understanding the business rules and the associated tables and relationships. Create some test data and test scenarios and test and refine your data model. Get a tested design on paper before jumping into physical database.
Good luck with your project.[/QUOTE]

thanks I will!
Canoehead is offline   Reply With Quote
Old 03-15-2019, 12:56 PM   #10
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 716
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Noobieissues

In your quest for knowledge, make sure you cover off how to name things, what not to do in naming, and what data types to use. Suggest you don't go any farther with this db; rather start over when you've covered some ground. I recommend you thoroughly understand normalization and entity/attribute relationships to start with. There is no one outcome as every db that serves a different purpose will be designed differently, but the approach and design principles are the same for all. My recommendation would be to post back with some sort of pic of relationships, posted sample db or outline of tables for scrutiny. Taking this out of Excel is the right decision IMHO, but you want to do it right, right?


Oh, and data types. Not everything has to be Double, and yes no fields shouldn't be text. I only looked at one table (which is why I mentioned about learning how to name things).

Last edited by Micron; 03-15-2019 at 12:57 PM. Reason: added info
Micron is offline   Reply With Quote
Old 03-15-2019, 01:22 PM   #11
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,280
Thanks: 10
Thanked 2,248 Times in 2,201 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Noobieissues

I looked over your database. Here's my notes:

1. Complete the Relationships tool. This tells us how your tables tie together.

2. Only alphanumeric and underscores in table/field names. You've got a ton of spaces and symbols which you shouldn't use (e.g. #, &, ', /, etc.)

3. Use the right datatype. There's a Yes/No field type, use it instead of text. Use Date/Time for dates, not text.

4. Put fields in the right table. Why is [OWNER OCCUPIED] a field in WATER HEATER/SOFTNER table? Shouldn't that be in Units table?

5. You don't have a Units table. You do have an Owners table, but can't an owner own more than one unit? If so, you need a table specifically for Units.

You should read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the process of properly structuring your tables. Read through a few tutorials, then give it a shot on your tables, fix the issues I addressed above and then screenshot your Relationship tool and post it back here so we can check what you came up with.
plog is offline   Reply With Quote
Old 03-15-2019, 01:43 PM   #12
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 716
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Noobieissues

A water heater is likely occupant owned vs rented, and if other bits of information about it are required (such as energy type, capacity, serial number etc etc, then I would agree with it being on its own. In all fairness, this one's going to be tough for a novice as there are quite a few things that could be either entities or attributes. So far, it's quite possible that every aspect of a rental might be its own table if one takes normalization to the nth degree. This means lockers, parking spots, inspections, and supporting (lookup) tables for things like inspection types. Comments like "not every unit gets a parking space" means holes in the table. Same with inspections and the like. Not saying it's always a bad thing to have nulls everywhere, just that there's a lot to consider. So far, I'm seeing a tendency to put things in fields rather than records.


Last edited by Micron; 03-15-2019 at 01:46 PM. Reason: clarification
Micron is offline   Reply With Quote
Reply

Tags
access , beginner user , table access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 01:14 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World