real estate database design

[deb]

I 'm creating a database with several commercial buildings, lots of tenants
and lots of car parking spaces - some spaces are included in the lease and
some are paid for separatly, also some are leased by people who dont lease
offices in the buildings (just the car space)

my question is - the car spaces need to be tracked and controlled but
they're not as important as the major office tenancies so i dont want to
include them in the list, i need to do them as a separate table but i need to
be able to show them in the form for each tenancy

cant quite wrap my head around how to work this

 

[Jeff Boyce]

It all starts with the data, and I'm having trouble visualizing your data
too.

What are the "things" about which you want to store data? I can infer that
properties is one, tenants is another, and parking spaces is a third. In a
relational database, if those are, indeed, your "things", you'd need a table
for each, PLUS, you'd need tables to handle the connection between them.

If "relational" and "normalization" are not familiar terms, plan to brush up
on them before proceeding.

And if you have experience using Excel, this will be even harder. Access
tables may look like spreadsheets, but if you treat them like spreadsheets,
both you and Access will have to work overtime to come up with work-arounds
for that 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[deb]

well i've managed to figure this out - sub form in my main form but...

now my lookup/search combo box is duplicating all the records because of the
car parking spaces

help

 

[John W. Vinson]

now my lookup/search combo box is duplicating all the records because of the
car parking spaces

Then correct the errors in your form or query design. If you would like help
doing so please post the structure of your tables and your form.

 

[deb]

ok - pretty simple so far i think

i have 5 tables
tenant - TenantID plus address info
property - PropertyID plus address info
location - LocationID, PropertyID plus location description (ie office 2A)
lease - LeaseID plus lease info
TenantToLease (join table) - TenantID, LocationID, LeaseID

Location joins to property and TenantToLease

TenantToLease joins also to Tenant

i think i have that structure right

i have a qry that includes all 5 tables plus from the Tenant tbl i have
added in [CompanyName] as the search field and this is where it comes unstuck

I have a form based on that qry and it all works fine except...because one
company (Tenant) can have several leases when i insert the search combo box
in the form it brings up each company several times (obviously because its
linking to several leases) - i need it to just show each company once because
i want to put all the leases in a subform on a separate tab to the main form

I'm missing something simple arn't I ?

deb

 

[deb]

in nutting through this myself i'm thinking this can be fixed by creating a
totals query for the search field but this doesnt work and i get this error

You created an SQL statement with an ALL, DISTINCT, or DISTINCTROW predicate
and an ORDER BY clause that contains a field not listed in the SELECT
statement. Remove the DISTINCT reserved word, or remove the specified field
from the ORDER BY clause.

i'm not really good with VBA so here i come to a dead end


--
deb

ok - pretty simple so far i think

i have 5 tables
tenant - TenantID plus address info
property - PropertyID plus address info
location - LocationID, PropertyID plus location description (ie office 2A)
lease - LeaseID plus lease info
TenantToLease (join table) - TenantID, LocationID, LeaseID

Location joins to property and TenantToLease

TenantToLease joins also to Tenant

i think i have that structure right

i have a qry that includes all 5 tables plus from the Tenant tbl i have
added in [CompanyName] as the search field and this is where it comes unstuck

I have a form based on that qry and it all works fine except...because one
company (Tenant) can have several leases when i insert the search combo box
in the form it brings up each company several times (obviously because its
linking to several leases) - i need it to just show each company once because
i want to put all the leases in a subform on a separate tab to the main form

I'm missing something simple arn't I ?

deb

Then correct the errors in your form or query design. If you would like help
doing so please post the structure of your tables and your form.

 

[Jeanette Cunningham]

Hi deb,
to eliminate all the copies of the same company, you willl need to narrow
down the number of tables in your query.
You need a search form that will let you choose a company.
Use this search form to show the leases for each company.
Base the search form on a query using only the tenant table.
You can set a property for the query on its property dialog - Find Unique
values and set it to yes.
This should give you each company only once, as long as you use only the
company field.
Once you include other fields such as the primary key from the tenant table,
you will get multiple copies of some companies.
Do a similar type of query for the combo for the search form.




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

ok - pretty simple so far i think

i have 5 tables
tenant - TenantID plus address info
property - PropertyID plus address info
location - LocationID, PropertyID plus location description (ie office 2A)
lease - LeaseID plus lease info
TenantToLease (join table) - TenantID, LocationID, LeaseID

Location joins to property and TenantToLease

TenantToLease joins also to Tenant

i think i have that structure right

i have a qry that includes all 5 tables plus from the Tenant tbl i have
added in [CompanyName] as the search field and this is where it comes
unstuck

I have a form based on that qry and it all works fine except...because one
company (Tenant) can have several leases when i insert the search combo
box
in the form it brings up each company several times (obviously because its
linking to several leases) - i need it to just show each company once
because
i want to put all the leases in a subform on a separate tab to the main
form

I'm missing something simple arn't I ?

deb

Then correct the errors in your form or query design. If you would like
help
doing so please post the structure of your tables and your form.

 

[deb]

thanks, that sort of solved my problem but with i minor tweek - i had to go
into the SQL and manually add DISTINCT to the code

yay!!!! fixed

thanks
--
deb

Hi deb,
to eliminate all the copies of the same company, you willl need to narrow
down the number of tables in your query.
You need a search form that will let you choose a company.
Use this search form to show the leases for each company.
Base the search form on a query using only the tenant table.
You can set a property for the query on its property dialog - Find Unique
values and set it to yes.
This should give you each company only once, as long as you use only the
company field.
Once you include other fields such as the primary key from the tenant table,
you will get multiple copies of some companies.
Do a similar type of query for the combo for the search form.




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

ok - pretty simple so far i think

i have 5 tables
tenant - TenantID plus address info
property - PropertyID plus address info
location - LocationID, PropertyID plus location description (ie office 2A)
lease - LeaseID plus lease info
TenantToLease (join table) - TenantID, LocationID, LeaseID

Location joins to property and TenantToLease

TenantToLease joins also to Tenant

i think i have that structure right

i have a qry that includes all 5 tables plus from the Tenant tbl i have
added in [CompanyName] as the search field and this is where it comes
unstuck

I have a form based on that qry and it all works fine except...because one
company (Tenant) can have several leases when i insert the search combo
box
in the form it brings up each company several times (obviously because its
linking to several leases) - i need it to just show each company once
because
i want to put all the leases in a subform on a separate tab to the main
form

I'm missing something simple arn't I ?

deb

now my lookup/search combo box is duplicating all the records because of
the
car parking spaces

Then correct the errors in your form or query design. If you would like
help
doing so please post the structure of your tables and your form.

.