Using a Table as a Referance - Unions

Hi, I was wondering if there was any union code that i could use to use a 
table as a reference.  what i mean is, if i had a main table that was related 
to this reference table by the primary key, and the reference table had 
fields choresponding to things in the in main table.  this way, any one 
record in the main table would have a value in the reference table determined 
by two of the fields in the main table.

i just realized how amazingly confusing i made that, so i will try to 
diagram it.

MAIN TABLE:
Joe - A - 1
Sally - B - 2
Kari - A - 2

Reference Table
-- - 1 - 2
A - x - y
B - z - q


where the goal is to be able to tie x with Joe, q with Sally, etc.  i think 
this could be done by making each field it's own table? but i would really 
like to keep all of the information in the same table (is this asking for 
something more excel oriented?)

Thank you,

Dan







0
Utf
1/4/2008 3:45:00 AM
access.reports 4434 articles. 0 followers. Follow

1 Replies
474 Views

Similar Articles

[PageSpeed] 20

That's not quite the way you design a relational database.

Presumably Joe, Sally etc are clients, and the reference table contains 
something (such as the preferences for each client.) If so you have a 
many-to-many relation between clients and preferences. You do not model this 
as many fields in the reference table. Instead you create a junction table 
between Clients and Preferences, with a *record* for each applicable 
combination of client and preference.

So the tables will be like this:
Client table, with fields
    ClientID        AutoNumber
    Surname      Text
    FirstName    Text
    ...

Preference table:
    PreferenceID  AutoNumber
    Preference     Text        (what this actually is)

ClientPreference table:
    ClientID
    PreferenceID

If you ever need to show a matrix with clients (down the left) and 
preferences (across the top), you use a crosstab query to generate that.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AtomicEnergy132" <AtomicEnergy132@discussions.microsoft.com> wrote in 
message
news:C92C5CB4-782A-4956-9941-553D5374A8BE@microsoft.com...
> Hi, I was wondering if there was any union code that i could use to use a
> table as a reference.  what i mean is, if i had a main table that was 
> related
> to this reference table by the primary key, and the reference table had
> fields choresponding to things in the in main table.  this way, any one
> record in the main table would have a value in the reference table 
> determined
> by two of the fields in the main table.
>
> i just realized how amazingly confusing i made that, so i will try to
> diagram it.
>
> MAIN TABLE:
> Joe - A - 1
> Sally - B - 2
> Kari - A - 2
>
> Reference Table
> -- - 1 - 2
> A - x - y
> B - z - q
>
>
> where the goal is to be able to tie x with Joe, q with Sally, etc.  i 
> think
> this could be done by making each field it's own table? but i would really
> like to keep all of the information in the same table (is this asking for
> something more excel oriented?) 

0
Allen
1/4/2008 3:59:09 AM
Reply:

Similar Artilces:

Using MSCRM remotely, pre-sale questions
Hello all, I want to know if I could make CRM part of my "virtual office". Would MSCRM work well in the following configuration? 1. Installed on MS SBS 2003 premium (I read the installing CRM on SBS 2003 instructions at the MS site) 2. The SBS would be installed on a dedicated server which would be hosted remotely at a quality server coloc 3. Up to five clients would access CRM from their PCs, laptops, or pocket PCs at their remote locations I don't know enough about SBS or MSCRM yet to know if the above would work well. Our company is made up of several people all ...

manually update field
currently we have a contract amount field for a table - many Many queries,forms, reports are presently using this table- we are manually recalculating this contract amount - I have created a query to update-recalculate the contract amount (based new workorders)- BUT!!!! - now I don't know how to handle past data - using this contract amount from the table - always query Past - present date - what am i missing- on how to handle??? Also don't know of a good way to find out all of the queries - reports, forms using this contract amount field - tried object dependencies sa...

cfgmgr32.dll is using obsolete deprecated CM_Detect_Resource_Confl
Hi, In my application (VC++ 2008 SP1 & WDK 6001.18000) I am using "Get_Next_Res_Des" to get device information. But the UMDH (User-Mode heap dump) call stack shows: CFGMGR32!CM_Detect_Resource_Conflict+00000065 CFGMGR32!CM_Get_Next_Res_Des_Ex+00000278 CFGMGR32!CM_Get_Next_Res_Des+00000017 ................................. And MSDN says "CM_Detect_Resource_Conflict:This function is obsolete and no longer supported in Windows 2000 and later versions of Windows. Use CM_Query_Resource_Conflict_List instead." http://msdn.microsoft.com/en-us/library/ms...

Replication in Access using USB drives.
How to program a replication of two computers with USB drives if you don't know what letter will a computer assign to a USB drive. Not sure I understand what you mean by "replication of two computers with USB drives", but you should be able to modify the code in http://www.mvps.org/access/api/api0003.htm at "The Access Web" to determine which drive letters are removable drives. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bigginer" <Bigginer@discussions.microsoft.com> wrote in message ...

WMP 12, Auto Playlist, combine criteria using Or
Hi @all, I couldn't figure out how to combine criteria using "or". The default setting is "and", which makes little sense to me. For instance, assume you might want to create a playlist that comprises more than one artist or more than one genre - how do you do that? TIA, Chris Auto playlists are explained pretty well at http://thewelltemperedcomputer.com/SW/WMP/Playlist.htm . Regards -- Tim De Baets http://www.bm-productions.tk thalur wrote: > Hi @all, > I couldn't figure out how to combine criteria using "or". The default ...

Multiple Domain Names on Outlook using the Exchange Server
Our company uses Outlook 2000 on exchange servers and all mail leaves with an envelope From address of @bear.com. I have a separate domain, user@maxrecovery.co.uk that i'd like to use on outgoing mails, so that all my recipients will see on the From: address, user@maxrecovery.co.uk and not user@bear.com and also not From: user@bear.com on behalf of user(Exchange) [user@maxrecovery.co.uk]. Is this possible? Also, Is it possible for me to receive mail if either user@bear.com or user@maxrecovery.co.uk is used? Thanks. ...

Referring to a control in a subform
I have a form (B1) with a subform (B11) showing let's say 4 linked items where the name of the linked field is "ID". When I doubleclick in a field in the item 3 in the subform I want to open the same form in an expanded wiev just showing only all information of item 3 only. I have made a macro for this function as follows: ID = Forms!B11!ID but this don't work. What shall that macro contain or do You have any other way to solve the problem ??? Tank You in advance Try this --- [Forms]![B1]![B11]![ID] -- KARL DEWEY Build a little - Test a little "Leif Tho...

creating forms with multiple tables
I have Access 2002, I created two tables, one that has all our customer information and the other will put in information pertaining to services rendered on a customers' pc. My question is, I created a form combining these two tables. I did it all as one form instead of using a sub form. When I go to the form to start entering information it will allow me to add the information for the customer but not the information for the computer. The table with the customer information has all the information in it, the table that will have the computer information gets added as the forms ...

redrawindows don't work in embedded, what can i use?
Hi, i would like to know the alternative of RedrawWindow(NULL, NULL, RDW_INVALIDATE | RDW_UPDATENOW) for Embedded VC++. I try Updatewindows but that's not work. If someone can write me the code equivalence the that line please. There the part of the code : void CDrawingView:nMouseMove(UINT nFlags, CPoint point) { if(m_Pan && GetDC()->PtVisible(point)) { if(m_pDrawing->isOpen()) { double OnePixel; VIEW drwview; m_pDrawing->GetViewProperties(&drwview); OnePixel = 1/(drwview.PPU*drwview.ZoomLevel); drwview.ViewLeft = drwview.ViewLeft ...

Using QSRules.dll in Visual Studio 2005 C#.NET
Hi All, I have added the QSRules as a reference and tried to access/ create objects, for example: QSRules.Session mySession = new QSRules.SessionClass(); now when i try: mySession.SOMETHING I don't get any options and get a error: Error 2 Invalid token '(' in class, struct, or interface member declaration RMS.VisualStudioTest\RMS.AccessClass\Class1.cs 12 24 RMS.AccessClass in the object browser i can see the methods for the class but can't access them through the code. Anyone got any ideas why or how to use the QSRules Dll in Visual Studio.NET 2005 using C...

Prevent combo box from updating a table
My application includes a Combo Box which gets it's values from a query, when I close the form it inserts the value which is in the combo box into the table from which that data came from. How do I prevent the insert? On Thu, 11 Mar 2010 20:33:28 GMT, "avtuvy" <u58699@uwe> wrote: >My application includes a Combo Box which gets it's values from a query, when >I close the form it inserts the value which is in the combo box into the >table from which that data came from. How do I prevent the insert? If the purpose of the combo box is just to find a r...

How do I create a combination chart and table with different data.
I am using Windows XP, and Microsoft Excel 2003. I have been asked to create scorecards with charts displaying rates graphically, and an attached table underneath with numerator and denominator data. The combination graphs in the custom charts try to graph and display all the data. Can I create a combination chart with the rates graphically displayed and the underlying numerator/denominator data presented in a linked table underneath the chart? Or do I need to create the graph and table separately? You would do better to make a separate chart and table, because chart data tables ...

how to make a table with 10 million rows in one column?
how to make a table with 10 million rows in one column? Num 1 2 3 .. .. .. 10,000,000 thanks, pemt WHY! -- Build a little, test a little. "pemt" wrote: > how to make a table with 10 million rows in one column? > > Num > 1 > 2 > 3 > . > . > . > 10,000,000 > > thanks, > > pemt On Mon, 22 Mar 2010 09:44:01 -0700, pemt <pemt@discussions.microsoft.com> wrote: >how to make a table with 10 million rows in one column? > >Num >1 >2 >3 >. >. >. >10,000,000 ...

Pivot table to calculate variance showing budget and actual.
Hi, I know this is rather simple, but I just could not get it right. I have this data: Expenese Type Amt Scenario Staff cost 200 Budget Admin cost 75 Budget Office rent 5000 Budget Staff cost 100 Actual Admin cost 45 Actual Office rent 300 Actual I am able to get the difference using the pivot table "Difference from", but it only showed that single column. I would like my pivot table to show: Expenese Type Budget Actual Variance Staff cost 200 100 1...

Navy Federal Credit Union won't do automatic updates
Since late July, my automatic updates for Navy Federal Credit Union Checking and Savings account quit updating, automatic or forced. I tried every day and nothing happened. Meanwhile, other banks updates were working fine. NFCU would tell me that the call connected on a certain date and time, but I would not have any transactions updated in my register on money. I started investigating a couple days ago, found information about an update Money was doing mid July so followed all the instructions to reset accounts without the password box shown on that updates page. I have followed t...

import data using macro
I'm trying to import data with exel. I import ten sets of data that are written in xls format. Here is my method for importing. In the data tab I press import external data and then press next and then add a space. I do the same process ten times and was wondering if I could streamline this process with a macro. Would it be possible to import one set and then teach the macro to import the next 9 data files in succession. The name of the files will change every time, but they always are in order (i.e. 718cr1, 718cr2, 718cr3, etc.). The 718 represents today's date, which is...

How do i start Macros using IF statements?
How can i use an IF statement to start a macro using data validation to get a list in a cell if the previous cell equals a specific word. Eg. IF C1 = "Bedroom" then it will trigger the macro to use data validation in the next cell to display a list of diffent number of bedrooms which the user can then choose. You need a worksheet change event macro to monitor C1 and respond accordingly. See: http://www.mvps.org/dmcritchie/excel/event.htm for complete details. Good Luck -- Gary's Student "xXx Katie xXx" wrote: > How can i use an IF statement to start a ma...

Cell reference
For example, I know I can refer to another worksheet in a cell formula as such: =Data!C6 However, Is there a way to refer to the name of the sheet based on the name of the sheet being a variable (ie. the contents of another cell?) THanks Matt Lawson Hi =INDIRECT("'" & A1 & "'!C6) where A1 stores your sheet name "Matt Lawson" wrote: > For example, I know I can refer to another worksheet in a cell formula as such: > =Data!C6 > > However, Is there a way to refer to the name of the sheet based on the name > of the sheet being a var...

Check box and make table query
I am trying to write a very basic make table query pulling some of the data from a form. the form has a check box, [MO] and a default value of -1 (this is to defaul all records as checked) If I run a datasheet veiw of the query, the table populates with the "-1" when I run the make table the field shows ?? and a data type of bianary. The query is pulling from the form as MOR:[forms]![Frm_RunNew]![MO] If I uncheck the box it show zero on view and null when run. any suggestions? I added Cbool to my query and all is fine now.. thanks to anyone who may have taken t...

Define Name use in Macros
I am not sure how or if you can use the Define Name function in a worksheet to assign a value in an Macro. I am using the command below to assign a value in the worksheet in the macro. However the user might make changes to the worksheet that makes this method invalid. If I use the Define Name function in excel and create a Name can I then us it to assign a value? Set Total_Assets = Sheets("Balance Sheet").Range("H55") You should definitely assign a name (Ctrl-F3) to this cell and all ranges your macros used, in case you or your users insert rows, etc. Set Total_...

Edit a table while in form view
I have a form that has multiple tabs and subforms. On one of the tabs is a subform with a recordsource that is a query. It shows data from a table in datasheet mode. In that table I have a field "Selected" which is a Yes/No field (checkbox). In form view, I want to be able to check the checkbox in the table in the subform. I don't have that field locked, what other settings should I check? Thanks, Akilah Can you update fields in the query if you open the query by itself? You need to determine whether it is the form that is locking the fields or the query itself. ...

XMLSerializer
I'm using XMLSerializer to serialize a hierarchy of class-based objects to XML. So far, this is working successfully by employing arrays to handle the multiplicity of child objects. However, I'm trying to employ the use of collection classes instead because there are methods I need to implement at that level. But when I instantiate the XMLSerializer, it gives the error: There was an error reflecting <Root Type>. If I try using <XmlArrayAttribute(<child object type>)> Public ... I get the same error. Is it possible to implement collection classes that the XMLSer...

Ho to Delete "Ghost" Pivot Tables
Hi, I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effecei...

pivot table cannot group that selection
I cannot get a column of data to group using a Pivot table. I have done all the suggestions I've ever know to do in my data which are: - i removed ALL blank entries - i did the "text to columns" trick to convert all data to text, then applied appropriate formatting for the data type. - i copied all data and pasted special to ensure only the values were entered. none of this works and i don't know what else to try. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

Using numbers in a cell
How do you change the default settings to allow all numbers to be show? For example 000635 will show up as just 635. Check your other post, you have 2 answers John "Texraid" wrote in message news:rt54p4hhagod2irv4dsu1ssiafendhurkp@4ax.com... > How do you change the default settings to allow all numbers to be > show? For example 000635 will show up as just 635. ...