Table Relationships - I think I am making more complicated than it should be

[PCSVentures]

I am extremely new to Access. I can not figure the relationship between table out in my attempt to create a work order tracking database for apartment maintenance. I am at the beginning stages of creating the database. The apartment complex is divided up into six(6) phases. Each phase of the Complex has anywhere from 3 to 10 buildings in the phase. The buildings can be a 4-plex, 8plex, or 12-plex.



I have a TBL-Phase which has two fields, a Primary Key PhaseID (autonumber) and PhaseName (shortText). I also have six Phase tables named TBL-Building-(name of the phase) with two fields with a primary key of BuildingID (autonumber) and BuildingNumber (shortText), and three tables named TBL-(4plex, 8plex, or 12plex) with only one field, a Primary key of UnitID (autonumber).

I need to establish table relationships or queries or something so that when a person is inputting the work order via a form, they select the phase, which then pre-populates the available buildings for that particular phase, then automatically selects the number of units for that particular building to select the unit to be worked on.

What am I missing? I cannot connect the dots to make this work?

[orange]

the beginning stages of creating the database

What does this mean in plain English?

Here is a link that may help you organize your database project.

There is more info here.

I recommend you start with a clear description of the "business" involved.
Don't be too quick to jump into a physical database.

Using pencil and paper, identify your tables and analyze what relates them to one another.

By working through one or two of the tutorials from RogersAccessLibrary mentioned in the link provided, you will learn (by experience) how to design your database. It could be the best 45- 60 minutes you'll spend on learning about tables and relationships - which are critical to a good database design.

Good luck.

[ranman256]

tPhase tbl
tBldgs tbl
tUnits tbl

the tBldg to me would have a sub table, tBldgMaint
for all service done to the bulding

BldgID
UnitNo (optional, blank if svc was on the building)
MaintDate
SvcDescr
SvcBy
Invoice#
Cost

[PCSVentures]

i have no problem drawing this on paper. I have done so several times. What i don't understand is how to make access do this. In the beginning stages , meant that i created the tables that i mentioned. I can Send a picture of the paper diagram if needed

[orange]

Yes, post your diagram.

[PCSVentures]

here is the paper diagram. when drilling down to the units, i will select a unit 1,2,3,4 or 1,2,3,4,5,6,7,8 or 1,2,3,4,5,6,7,8,9,10,11,12 to single out the work area CCF03062018.pdf

[PCSVentures]

i will review the links later tonight.

[ssanfu]

From your description, it sounds like you are missing linking fields (the FK fields).

From your description and the sketch, maybe start with this:

[PCSVentures]

Thanks! I will review the foreign keys.

I really dont know what i am missing. Ive looked at everything from cascading comboboxes, lookups, etc. the concepts make sense to me in theory( i could draw them on paper). but i just dont know the commands or procedures to make it work in access. I did make it work in google forms using conditional formating, but google forms doesn't do everything i want it to do as far as queries and reports.

[PCSVentures]

What does this mean in plain English?

Here is a link that may help you organize your database project.

There is more info here.

I recommend you start with a clear description of the "business" involved.
Don't be too quick to jump into a physical database.

Using pencil and paper, identify your tables and analyze what relates them to one another.

By working through one or two of the tutorials from RogersAccessLibrary mentioned in the link provided, you will learn (by experience) how to design your database. It could be the best 45- 60 minutes you'll spend on learning about tables and relationships - which are critical to a good database design.

Good luck.

Orange - would you be so kind as to repost the link to the RogersAccessLibrary. the links you provided both direct back to MS.

Thanks!

I think I found the site. no need to repost.

[ranman256]

you need a continuous form for each table.
you need a single record form for bldng ,and a subform for maint.
same with single unit
use the form wizard, its much faster.

unit form can have many subforms (i use a tab control and a single subform):
maint
rent history
pay history (more for the tenant that unit)

[orange]

This is the corrected url for info on database planning and design
https://www.accessforums.net/showthr...133#post352133