A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Modify existing table for more details?



 
 
Thread Tools Display Modes
  #1  
Old February 23rd, 2010, 09:21 PM posted to microsoft.public.access.tablesdbdesign
Shannon[_3_]
external usenet poster
 
Posts: 3
Default Modify existing table for more details?

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.
  #2  
Old February 23rd, 2010, 10:05 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Modify existing table for more details?

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.
.

  #3  
Old February 23rd, 2010, 11:03 PM posted to microsoft.public.access.tablesdbdesign
Shannon[_3_]
external usenet poster
 
Posts: 3
Default Modify existing table for more details?

On Feb 23, 1:05*pm, KARL DEWEY
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




  #4  
Old February 23rd, 2010, 11:07 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Modify existing table for more details?

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



"Shannon" wrote in message
...
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.



  #5  
Old February 24th, 2010, 01:16 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Modify existing table for more details?

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
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




.

  #6  
Old February 24th, 2010, 11:24 PM posted to microsoft.public.access.tablesdbdesign
Shannon[_3_]
external usenet poster
 
Posts: 3
Default Modify existing table for more details?

On Feb 23, 4:16*pm, KARL DEWEY
wrote:
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
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


.- 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?
  #7  
Old February 25th, 2010, 01:33 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Modify existing table for more details?

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.


"Shannon" wrote:

On Feb 23, 4:16 pm, KARL DEWEY
wrote:
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
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


.- 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?
.

  #8  
Old February 25th, 2010, 06:28 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Modify existing table for more details?

Yes, I will be glad to look at it!

Steve


"Shannon" wrote in message
...
On Feb 23, 4:16 pm, KARL DEWEY
wrote:
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
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


.- 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?


  #9  
Old February 25th, 2010, 06:36 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Modify existing table for more details?

"Steve" wrote in message
...
Yes, I will be glad to look at it!

Steve



For how much are you offering your questionable services?


  #10  
Old February 26th, 2010, 12:46 AM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default Modify existing table for more details?


"Steve" schreef in bericht ...
Yes, I will be glad to look at it!

Steve


Amazing... but I guess the bill comes later ??

--
Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.500 pageloads... it's a shame !!)

For those who don't 'agree' with this mail , because $teve was 'helping' with his post...
We warned him a thousand times... Sad, but he is not willing to stop advertising...

He is just toying with these groups... advertising like hell... on and on... for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'Stop$teve' and you won't see these mails....

Arno R


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.