Noobieissues (1 Viewer)

Canoehead

New member
Local time
Yesterday, 20:26
Joined
Mar 15, 2019
Messages
4
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 🇨🇦
 

Micron

AWF VIP
Local time
Yesterday, 23:26
Joined
Oct 20, 2018
Messages
2,772
I doubt a "couple" of tables will cut it - at least not if properly normalized.
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).
 

Canoehead

New member
Local time
Yesterday, 20:26
Joined
Mar 15, 2019
Messages
4
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
 

Minty

AWF VIP
Local time
Today, 04:26
Joined
Jul 26, 2013
Messages
6,987
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.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 22:26
Joined
Feb 28, 2001
Messages
16,826
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.
 

mike60smart

Registered User.
Local time
Yesterday, 20:26
Joined
Aug 6, 2017
Messages
514
Hi

Can you upload a zipped copy of your Db so we can see what table structures you currently have?
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 23:26
Joined
Jan 23, 2006
Messages
13,102
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.
 

Canoehead

New member
Local time
Yesterday, 20:26
Joined
Mar 15, 2019
Messages
4
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:

Canoehead

New member
Local time
Yesterday, 20:26
Joined
Mar 15, 2019
Messages
4
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!
 

Micron

AWF VIP
Local time
Yesterday, 23:26
Joined
Oct 20, 2018
Messages
2,772
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:

plog

Banishment Pending
Local time
Yesterday, 22:26
Joined
May 11, 2011
Messages
9,814
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.
 

Micron

AWF VIP
Local time
Yesterday, 23:26
Joined
Oct 20, 2018
Messages
2,772
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:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom