Modify existing table for more details?
in [Tables]
|
Prev: Test
Next: Remote control of tabls
From: Shannon on 23 Feb 2010 15:21 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.
From: KARL DEWEY on 23 Feb 2010 16:05 >>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. -- Build a little, test a little. "Shannon" wrote: > 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. > . >
From: Shannon on 23 Feb 2010 17:03 On Feb 23, 1:05 pm, KARL DEWEY <KARLDE...(a)discussions.microsoft.com> 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. > > -- > Build a little, test a little. > > > > "Shannon" wrote: > > 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
From: Steve on 23 Feb 2010 17:07 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 santus(a)penn.com "Shannon" <shannon.nelms(a)gmail.com> wrote in message news:25bcc9e1-7712-4732-9713-0baefd18bf7e(a)c34g2000pri.googlegroups.com... >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.
From: KARL DEWEY on 23 Feb 2010 19:16
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. "Shannon" wrote: > On Feb 23, 1:05 pm, KARL DEWEY <KARLDE...(a)discussions.microsoft.com> > 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. > > > > -- > > Build a little, test a little. > > > > > > > > "Shannon" wrote: > > > 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 > > > > > . > |