Modify existing table for more details?

[Shannon]

I have a table of estimates. Each estimate can have multiple locations
stored as a text field. New need: I want to store the sizes of each
location. Example is -Old method: EstNo= 200 which has Location =
bedroom, Location= Family room. New need is: EstNo 200, Location
= bedroom with Size= 100 and Location= Family room size = 125. I
assume I need to split the Location field into its own table with a
Location ID field, Location field, location size field and EstNo
field. Also, How would I go about correcting the existing estimates
that have no location sizes entered. Hope I discribed this clearly
as I am a newby.

 

[KARL DEWEY]

I assume I need to split the Location field into its own table
You did not say how your table(s) is structured now - field names with
datatype or provide sample data.
How will you use the Size? Will you be adding or multiplying by some other
factors?
Not knowing the above I would guess to set all old records = 0.

 

[Shannon]

You did not say how your table(s) is structured now - field names with
datatype or provide sample data.
How will you use the Size?  Will you be adding or multiplying by some other
factors?


Not knowing the above I would guess to set all old records = 0.

--
Build a little, test a little.





- Show quoted text -

Thanks for the response and here is the info requested.
tblBidInfo
EstNo=number
Descrip=txt
JobCatId= lookup table txt
Other misc fields

tblBidDet
BidId= autonum
EstNo-=number ~ linked to tblBidInfo
Location= txt values recvd from lookup table
Other misc fields

I was thinking I need to add a new tblLocations with the following
fields,
LocId = autonum
LocName = txt ~ recvd from exisitng Lookup table of txt listings

I get confused here.
I will be using Location size in simple math calcs. Basically Estno
2010.1 has a bedroom thats 100 sf. All the listed work in that
location will be reported and then calculated on the size [ bedroom
work costs 8,000 / 100 sf = 80 psf cost. There are other calcs and
percentage operations I will do in reports based upon that data.]

Again, thanks for you input.
Shannon

 

[Steve]

Hi Shannon,

What do you think of this design for your tables:
TblLocation
LocationID
LocationName
Size

TblEstimate
EstimateID
EstimateDate
EstimateAmount
<other estimate fields>

TblLocationsIncludedInEstimate
LocationsIncludedInEstimateID
EstimateID
LocationID

Importing existing estimates that have no location sizes entered is not a
problem with the above design. The Size field in TblLocation will just be
blank.

Steve
(e-mail address removed)

 

[KARL DEWEY]

It seems to me you do not need another table as your tblBidDet.Location is
not a true location but a text description using a lookup of common names
ffor areas.

I would just add a Size field.

--
Build a little, test a little.

You did not say how your table(s) is structured now - field names with
datatype or provide sample data.
How will you use the Size? Will you be adding or multiplying by some other
factors?


Not knowing the above I would guess to set all old records = 0.

--
Build a little, test a little.





- Show quoted text -

Thanks for the response and here is the info requested.
tblBidInfo
EstNo=number
Descrip=txt
JobCatId= lookup table txt
Other misc fields

tblBidDet
BidId= autonum
EstNo-=number ~ linked to tblBidInfo
Location= txt values recvd from lookup table
Other misc fields

I was thinking I need to add a new tblLocations with the following
fields,
LocId = autonum
LocName = txt ~ recvd from exisitng Lookup table of txt listings

I get confused here.
I will be using Location size in simple math calcs. Basically Estno
2010.1 has a bedroom thats 100 sf. All the listed work in that
location will be reported and then calculated on the size [ bedroom
work costs 8,000 / 100 sf = 80 psf cost. There are other calcs and
percentage operations I will do in reports based upon that data.]

Again, thanks for you input.
Shannon




.

 

[Shannon]

It seems to me you do not need another table as your tblBidDet.Location is
not a true location but a text description using a lookup of common names
ffor areas.  

I would just add a Size field.

--
Build a little, test a little.

Thanks for the response and here is the info requested.
tblBidInfo
  EstNo=number
  Descrip=txt
  JobCatId= lookup table txt
 Other misc fields

tblBidDet
 BidId= autonum
 EstNo-=number ~ linked to tblBidInfo
 Location= txt  values recvd from lookup table
  Other misc fields

I was thinking I need to add a new tblLocations with the following
fields,
LocId = autonum
LocName = txt ~ recvd from exisitng Lookup table of txt listings

I get confused here.
I will be using Location size in simple math calcs. Basically Estno
2010.1 has a bedroom thats 100 sf.  All the listed work in that
location will be reported and then calculated on the size [ bedroom
work costs 8,000 / 100 sf = 80 psf cost. There are other calcs and
percentage operations I will do in reports based upon that data.]

Again, thanks for you input.
Shannon

.- Hide quoted text -

- Show quoted text -

To Karl: I believe that I must move the Location field to it's own
table for this example. Estno 200 can have a bedroom thats 100 sf.
EstNo 201 can have a bedroom thats 175 sf. So, my logic is One
Estimate can have many locations[rooms lets say] of differant sizes.
Another Estimate might have the same location name, but differant
sizes.

To Steve: I will try your suggestion.

Is it ok to post a pdf of the relationship window once I get it how I
think it should be?

 

[KARL DEWEY]

You would have two tables in a one-to-many relationship.
The first for the basic bib information --
BidID - primary key
Client -
Bid - $'s
Bid_Date
etc.

BidComponents --
BidCompID - primary key
BidID - foreign key
Location -
Size -
etc.

And possibly a third table --
BidCompMaterial --
BidCompMatID - primary key
BidCompID - foreign key
Material -
Cost -
Labor -
etc.

--
Build a little, test a little.

It seems to me you do not need another table as your tblBidDet.Location is
not a true location but a text description using a lookup of common names
ffor areas.

I would just add a Size field.

--
Build a little, test a little.

On Feb 23, 1:05 pm, KARL DEWEY <[email protected]>
wrote:
I assume I need to split the Location field into its own table

You did not say how your table(s) is structured now - field names with
datatype or provide sample data.
How will you use the Size? Will you be adding or multiplying by some other
factors?

How would I go about correcting the existing estimates that have no location sizes entered.

Not knowing the above I would guess to set all old records = 0.

:
I have a table of estimates. Each estimate can have multiple locations
stored as a text field. New need: I want to store the sizes of each
location. Example is -Old method: EstNo= 200 which has Location =
bedroom, Location= Family room. New need is: EstNo 200, Location
= bedroom with Size= 100 and Location= Family room size = 125. I
assume I need to split the Location field into its own table with a
Location ID field, Location field, location size field and EstNo
field. Also, How would I go about correcting the existing estimates
that have no location sizes entered. Hope I discribed this clearly
as I am a newby.
.- Hide quoted text -

- Show quoted text -

Thanks for the response and here is the info requested.
tblBidInfo
EstNo=number
Descrip=txt
JobCatId= lookup table txt
Other misc fields

tblBidDet
BidId= autonum
EstNo-=number ~ linked to tblBidInfo
Location= txt values recvd from lookup table
Other misc fields

I was thinking I need to add a new tblLocations with the following
fields,
LocId = autonum
LocName = txt ~ recvd from exisitng Lookup table of txt listings

I get confused here.
I will be using Location size in simple math calcs. Basically Estno
2010.1 has a bedroom thats 100 sf. All the listed work in that
location will be reported and then calculated on the size [ bedroom
work costs 8,000 / 100 sf = 80 psf cost. There are other calcs and
percentage operations I will do in reports based upon that data.]

Again, thanks for you input.
Shannon

.- Hide quoted text -

- Show quoted text -

To Karl: I believe that I must move the Location field to it's own
table for this example. Estno 200 can have a bedroom thats 100 sf.
EstNo 201 can have a bedroom thats 175 sf. So, my logic is One
Estimate can have many locations[rooms lets say] of differant sizes.
Another Estimate might have the same location name, but differant
sizes.

To Steve: I will try your suggestion.

Is it ok to post a pdf of the relationship window once I get it how I
think it should be?
.

 

[Steve]

Yes, I will be glad to look at it!

Steve

It seems to me you do not need another table as your tblBidDet.Location is
not a true location but a text description using a lookup of common names
ffor areas.

I would just add a Size field.

--
Build a little, test a little.

Thanks for the response and here is the info requested.
tblBidInfo
EstNo=number
Descrip=txt
JobCatId= lookup table txt
Other misc fields

tblBidDet
BidId= autonum
EstNo-=number ~ linked to tblBidInfo
Location= txt values recvd from lookup table
Other misc fields

I was thinking I need to add a new tblLocations with the following
fields,
LocId = autonum
LocName = txt ~ recvd from exisitng Lookup table of txt listings

I get confused here.
I will be using Location size in simple math calcs. Basically Estno
2010.1 has a bedroom thats 100 sf. All the listed work in that
location will be reported and then calculated on the size [ bedroom
work costs 8,000 / 100 sf = 80 psf cost. There are other calcs and
percentage operations I will do in reports based upon that data.]

Again, thanks for you input.
Shannon

.- Hide quoted text -

- Show quoted text -

To Karl: I believe that I must move the Location field to it's own
table for this example. Estno 200 can have a bedroom thats 100 sf.
EstNo 201 can have a bedroom thats 175 sf. So, my logic is One
Estimate can have many locations[rooms lets say] of differant sizes.
Another Estimate might have the same location name, but differant
sizes.

To Steve: I will try your suggestion.

Is it ok to post a pdf of the relationship window once I get it how I
think it should be?

 

[John... Visio MVP]

Yes, I will be glad to look at it!

Steve

For how much are you offering your questionable services?