Best Practice Value list or linked table?

I am beginning to wonder if i overcomplicate my database. i am going to add a 
field to my T_partnumbers for unit of measure (UOM) and can not decide if i 
should do it like i have in the past and use a combo (on my forms of course) 
with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid in 
my part numbers table or just use a value list in my combo and save the 
actual UOM abbreviation in my P/N table. 

What is a good determining factor for which method to use?
And What to store in my table LB, OZ, EA, ect. or their respective ID's? 

I feel linked tables for everything is becoming complicated as i view my 
T_Partnumbers all of the data is Greek and meaningless unless i query and 
join the appropriate tables, Maybe this is the Database method and i am 
afraid to further stray from anything humanly recognizable..

Thank you for your thoughts..
Barry 
0
Utf
11/19/2009 5:56:05 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

4 Replies
700 Views

Similar Articles

[PageSpeed] 22

Barry

It sounds as though you are trying to make sense of the raw tables.  Stop 
now!

Access tables store data.  Access forms (and reports) display data (via 
queries).

Don't try to make an Access table work like a spreadsheet -- it isn't one.

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

"Barry A&P" <BarryAP@discussions.microsoft.com> wrote in message 
news:CF1E4400-F1E8-4FF1-9DAF-8FED6B0EAA8C@microsoft.com...
>I am beginning to wonder if i overcomplicate my database. i am going to add 
>a
> field to my T_partnumbers for unit of measure (UOM) and can not decide if 
> i
> should do it like i have in the past and use a combo (on my forms of 
> course)
> with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid 
> in
> my part numbers table or just use a value list in my combo and save the
> actual UOM abbreviation in my P/N table.
>
> What is a good determining factor for which method to use?
> And What to store in my table LB, OZ, EA, ect. or their respective ID's?
>
> I feel linked tables for everything is becoming complicated as i view my
> T_Partnumbers all of the data is Greek and meaningless unless i query and
> join the appropriate tables, Maybe this is the Database method and i am
> afraid to further stray from anything humanly recognizable..
>
> Thank you for your thoughts..
> Barry 


0
Jeff
11/19/2009 7:09:51 PM
A combo can be from a table with only one field and use it bound.

-- 
Build a little, test a little.


"Barry A&P" wrote:

> I am beginning to wonder if i overcomplicate my database. i am going to add a 
> field to my T_partnumbers for unit of measure (UOM) and can not decide if i 
> should do it like i have in the past and use a combo (on my forms of course) 
> with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid in 
> my part numbers table or just use a value list in my combo and save the 
> actual UOM abbreviation in my P/N table. 
> 
> What is a good determining factor for which method to use?
> And What to store in my table LB, OZ, EA, ect. or their respective ID's? 
> 
> I feel linked tables for everything is becoming complicated as i view my 
> T_Partnumbers all of the data is Greek and meaningless unless i query and 
> join the appropriate tables, Maybe this is the Database method and i am 
> afraid to further stray from anything humanly recognizable..
> 
> Thank you for your thoughts..
> Barry 
0
Utf
11/19/2009 7:34:01 PM
On Thu, 19 Nov 2009 09:56:05 -0800, Barry A&P
<BarryAP@discussions.microsoft.com> wrote:

>I am beginning to wonder if i overcomplicate my database. i am going to add a 
>field to my T_partnumbers for unit of measure (UOM) and can not decide if i 
>should do it like i have in the past and use a combo (on my forms of course) 
>with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid in 
>my part numbers table or just use a value list in my combo and save the 
>actual UOM abbreviation in my P/N table. 
>
>What is a good determining factor for which method to use?
>And What to store in my table LB, OZ, EA, ect. or their respective ID's? 
>
>I feel linked tables for everything is becoming complicated as i view my 
>T_Partnumbers all of the data is Greek and meaningless unless i query and 
>join the appropriate tables, Maybe this is the Database method and i am 
>afraid to further stray from anything humanly recognizable..

Hi Barry,

I suggest avoiding Values Lists entirely.  They must be maintained
separately from the data itself, so you run the risk of them being
redundant or out of sync.

You can use the lookup table as you describe.  If you like, you can
use the abbreviation as the primary key and join with that.  Then
you'll be able to see the abbreviation without the joining table.

Another example of this is a StateProvince table.  If you know the
abbreviations will be unique, you can use them for the primary key
instead of an autonumber.

But I agree with other posters that you shouldn't be looking at your
tables directly to see what's in them.  All your related information
should be joined in from other tables using queries, forms or reports.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
0
Armen
11/20/2009 11:38:19 PM
Thank You Armen



"Armen Stein" wrote:

> On Thu, 19 Nov 2009 09:56:05 -0800, Barry A&P
> <BarryAP@discussions.microsoft.com> wrote:
> 
> >I am beginning to wonder if i overcomplicate my database. i am going to add a 
> >field to my T_partnumbers for unit of measure (UOM) and can not decide if i 
> >should do it like i have in the past and use a combo (on my forms of course) 
> >with a row source to a T_UOM with Ea, LB, OZ, Ft Ect. and store the UOMid in 
> >my part numbers table or just use a value list in my combo and save the 
> >actual UOM abbreviation in my P/N table. 
> >
> >What is a good determining factor for which method to use?
> >And What to store in my table LB, OZ, EA, ect. or their respective ID's? 
> >
> >I feel linked tables for everything is becoming complicated as i view my 
> >T_Partnumbers all of the data is Greek and meaningless unless i query and 
> >join the appropriate tables, Maybe this is the Database method and i am 
> >afraid to further stray from anything humanly recognizable..
> 
> Hi Barry,
> 
> I suggest avoiding Values Lists entirely.  They must be maintained
> separately from the data itself, so you run the risk of them being
> redundant or out of sync.
> 
> You can use the lookup table as you describe.  If you like, you can
> use the abbreviation as the primary key and join with that.  Then
> you'll be able to see the abbreviation without the joining table.
> 
> Another example of this is a StateProvince table.  If you know the
> abbreviations will be unique, you can use them for the primary key
> instead of an autonumber.
> 
> But I agree with other posters that you shouldn't be looking at your
> tables directly to see what's in them.  All your related information
> should be joined in from other tables using queries, forms or reports.
> 
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
>  
> .
> 
0
Utf
11/24/2009 7:07:02 AM
Reply:

Similar Artilces:

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

Can E-mail recipient policy be linked to the OU membership?
Running Exchange 2003 (mixed mode) and Windows 2003 server (Windows 2000 mixed mode): I'd like to create a e-mail recipient policy that is linked to the different OU's that are created on the system. Is it possible to link the policy to a specific OU or do I need to create groups on the AD to control which recipients a policy applies to? Martin Moustgaard Yes, you can create a recipient policy that looks to just a specific OU. It is done through LDAP queries. Here are some references to articles and whitepapers that discuss how to work with recipient policies. 249299.KB.EN-US HO...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Updating External Links Excel 2000 vs 2002/2003
I have a situation where I'm using Excel 2000 with a workbook containing references to another workbook. When opening the first workbook & the second workbook is not available, you can say "no" to the update external links, and still see all values as they were when the first workbook was last closed. However, when the same workbook is opened in Excel 2002 or 2003, the external links specified only as a cell reference show the proper data (e.g, =wbkname!E1), but when they are Excel formulae (specifically a SUMIF), I'm getting a #VALUE! error in the pertinent cells Is th...

basic pivot table group
I have looked at some basic Pivot Table tutorials, but I have a very basic question not answered by their examples. . . . I have data in a spreadsheet formatted like this, each row representing an event: Timestamp Process Message ID 11:23:45:123 processA message123 11:23:45:124 processB message123 11:23:45:136 processC message123 11:23:46:123 processA message456 11:23:47:123 processB message456 11:23:47:678 processC message456 Can a pivot table help me reformat the data like below? ...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where $G$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be$e83a5e80$a301280a@phx.gbl... > when we update our contacts how can we g...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...