The Crux of my problem is how do I relate [Company,
Subdivision, and Lot number] as one "Field" in a drop down
box for users to selected as the lot they want to add a
construction progress too. Ie the 55 construction actives
that take place during construction through sale. The user
needs to add a new company and or a new subdivision and
it's lot numbers on the fly. They cannot create a table
(they don't know how nor want to know how) . They want to
add companies and subdivisions on the fly from a form.
Unless you know a way to create a new table automatically
upon entry of a new subdivision? They just want to type in
a new company or subdivision from a form or forms then
have it show up in the list of companies to add a
subdivisions too, then in the list of subdivisions to add
lot numbers too. Then for regular users to select that
exact Company/Subdivision/lot# to add construction
actions to. The Tables must exist already. It would be a
mess to have to create a table every time they start a new
business or subdivision. They want a finished DB for
regular users to use. (no hired DB expert to create or
modify the DB to add New companies or subdivisions.
What they are wanting to do is be able to look at a
Company (of their 4 existing or add any new company they
may open in the future). Then on selection of one of the
companys, See all the subdivisions owned by that company
then on selection of a subdivision and see all the lots
under construction in that subdivision . Then look at
Items /actions related to that one piece of property. Ie.
Other tables.
The other tables have several fields each a couple of
examples are
[Temporary Power Poll]
Expected Install Date: (short date format)
Install Date: (short date format)
Complete Yes/No
Actual Completion Date: (short date format)
Responsiable Person = List box employee titles ie..
forman, laborer, Vice pres, etc.
Date power pole record entered in to DB= Auto date
[ Grading ]
Expected Grade Date: (short date format)
Actual Grade Date: (short date format)
Complete : Yes/No
Completion Date: (short date format)
Responsiable Person: = List box employee titles ie..
forman, laborer, Vice pres, etc.
Date: Grading record entered = Auto date
[Framing]
[Lot Sold]
[Lot under contract] etc etc.
Anything you can imagine that would take place from
purchase of subdivision property
Through final sale of a House built on a "LOT" with in
that subdivision
Each of these actions above all have several fields and
dates. But each will be entered at a different time as
construction goes on.
I have 55 of these actions/actives like above that belong
to only 1 "lot" number ie. (company+subdivision) then lot
#'s. But remember I can have ten lots each with the #1 as
it's lot number. I could have a subdivision with the same
name as another subdivision but owned by one of their
different companies. Example: on repeted lot #
Company Subdivisions Lot #'s
New Ridge Homes Park Place 101a, 102a, 103a,
101b, etc.
Ivy Crest 101a, 102a, 103a
etc.
Big Lake B40, b41, j32, etc
Fountain Homes New Park Place A100, A101, B101
etc.
Ivy Crest West A100, A101, A102,
Everything I have done to combine [Company, Subsivision,
Lot Number] shows up in the drop down box fine but upon
selection only one of the three (company, Sub, Lot #) will
be displayed in the field after selection. So if I let any
single item of the three (company, Sub, Lot #) remain in
the field alone after selection I end up garbage because
the actions to be entered are based on all three (company,
Sub, Lot #) equaling a unique record on the total of the 3
(company, Sub, Lot #) but not unique for any single one.
ie. just company just Subdivision or any single one of the
three.
This is why I think I need a new way to design the
tables? Some way to extablish (company, Sub, Lot #) as a
single field or record to take the 55 actions upon.
I hope this makes sence, I will be here all night and all
day tomorrow until I can figure this out. I know I am just
missing something stupid, but I have wasted 60+ house and
am losing my mind
Thank to all
Losing it bad
Keith J
Quote:
>-----Original Message-----
>As a start, you should not have 55 activities tables.
You should have only one
Quote:
>activity table that lists all the activities that can
take place. Then you have
Quote:
>another table that could be named LotActivities, that
table could consist of
Quote:
>something like:
>LotActivities Table
>CompanyID --> from Company table
>SubDivisionID --> from subdivision Table
>LotID
>ActivityID --> From New activity table
>ActivityDate
>(any other fields that are related to the activity, such
as inspector
>I would have a complex index no duplicates on CompanyId +
SubDivisionID + LotID
Quote:
>+ ActivityID. This assumes that each activity takes
place only one time per
Quote:
>combination. IF that is not the case you might add
Activity Date to your index.
Quote:
>This gives you a great deal more flexability since you
can add a new record to
Quote:
>the activity table and then have that available without
any additional coding.
Quote:
>If you cannot redesign your table structure, post back
and perhaps someone can
Quote:
>suggest methods to help you within the current structure.
>> I am sorry but I am going crazy! Over 60 hours invested
>> and I cannot move forward!
>> My question will make everyone laugh. I am so upset I
cant
>> even poet this right 3rd attempt.
>> I have 3 tables that make up a "Record" that I need to
>> relate other tables to.
>> Example= CompanyName (Table 1), SubdivisionName(Table
2),
>> Lot Numbers(Table3)
>> Company Table
>> CA
>> CB
>> CC
>> SD No dups allowed
>> Subdivision Table
>> S1
>> S2
>> S3
>> S4 Again no dups allowed
>> Lot Numbers(Table3) has two fields: one a list box from
>> SubdivisionName(Table 2) and then a text Lot Number
field
>> that is indexed with Subdivision field to not allow dups
>> on entry.
>> A user cannot input: SubdivisionA, Lot Number 1, more
than
>> once (I need this)
>> Example:
>> SUBDIVISION LOT NUMBER
>> SA 1 ------Error below
>> SB 1
>> SB 2
>> SB 3
>> SA 1 = Error because of dup record
on
>> the two fields index.
>> This prevents dup entrys of lot # to any one
Subdivision.
>> I need to relate these 3 tables as a unique record to
have
>> data in other tables related to
>> the 3 combined.
>> In other words I need a user to be able to select:
>> Company [CA] , Subdivision, [XYZ], Lot Number [23] then
up
>> date an action from another table.
>> Example: Company [CA] , Subdivision, [XYZ], Lot Number
>> [23] Has had the construction permit issued to that
>> specific lot.
>> Company CA, Subdivision XYZ, Lot Number 1, Construction
>> Permit= Yes
>> The Construction permit table has to beable to select
the
>> exact lot number related to its Company and
subdivision, I
>> can have many lot number 1's but only one number 1 per
>> subdivision.
>> There are 55 updates to each lot , Power pole, Grading,
>> Framing, etc etc
>> Example: CompanyName (Table 1), has X number of
>> Subdivisions from SubdivisionName(Table 2) related to
it.
>> And SubdivisionName(Table 2) each subdivision has X
number
>> of lots in each subdivision related from Lot Numbers
>> (Table3)
>> Each company can have any number of subdivisions under
>> It's control and each subdivision can have any number
>> of "lots"= [undeveloped properties] related to it.
>> I need to be able to relate other tables to [CompanyName
>> (Table 1), SubdivisionName(Table 2), Lot Numbers
(Table3)]
>> Example: Record 1= Company A (from company table),
>> Subdivision XYZ (from subdivision table), Lot Number 1=
>> one record that the user needs to select to add other
>> actions to it.
>> Company Subdivision Lot Numbers
>> CA------------------------XYZ----------------- 1
>> Each lot number = 55 other 2
>> Actions /progress tables
>> 3
>> CB------------------------ABC------ Many lot #
>> DEF----- Many Lot #
>> How do I link the other 55 tables si to see CompanyName
>> (Table 1), SubdivisionName(Table 2), Lot Numbers(Table3)
as
>> a single (Lot)/ Record.
>> Please feel free to E-mail me or what ever! I am truly
>> exausted BURNTOUT!!! Help if you know a wat to design
this.
>> The users want to create Companies and Subdivisions on
the
>> fly. The will not be able to create new tables so their
>> input nust go into exsisting tables I create to start
with.
>.
Design Help PLEASE PLEASE PLEASE!