If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
real estate database design
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 -- deb |
#2
|
|||
|
|||
real estate database design
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" wrote in message ... 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 -- deb |
#3
|
|||
|
|||
real estate database design
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 -- deb "deb" wrote: 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 -- deb |
#4
|
|||
|
|||
real estate database design
On Mon, 2 Nov 2009 17:41:01 -0800, deb wrote:
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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
real estate database design
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 "John W. Vinson" wrote: On Mon, 2 Nov 2009 17:41:01 -0800, deb wrote: 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. -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
real estate database design
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 "deb" wrote: 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 "John W. Vinson" wrote: On Mon, 2 Nov 2009 17:41:01 -0800, deb wrote: 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. -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
real estate database design
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 "deb" wrote in message ... 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 "John W. Vinson" wrote: On Mon, 2 Nov 2009 17:41:01 -0800, deb wrote: 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. -- John W. Vinson [MVP] . |
#8
|
|||
|
|||
real estate database design
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 "Jeanette Cunningham" wrote: 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 "deb" wrote in message ... 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 "John W. Vinson" wrote: On Mon, 2 Nov 2009 17:41:01 -0800, deb wrote: 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. -- John W. Vinson [MVP] . . |
Thread Tools | |
Display Modes | |
|
|