Loading Table with Associated Foreign Table

Hope I've expressed my question correctly in the title.

I have two separate tables.

Table1:

fldIDRepairName    << AutoNumber and Primary Key
fldRepairName        << Index, Unique
fldIDGroupName     << Link to Foreign Table

Table 2 ( I call this Foreign):

fldIDGroupName      <<AutoNumber and Primary Key
fldGroupName

==================
If trying to load an alphabetical list of the "fldRepairName" in Table 1:

and

 at the same time get the "fldGroupName" into a second list box??

===================
If I loop "Table 1"

Do Until .EOF

     List1.AddItem !fldRepairName

      >>> Do I need another procedure (function)
              to Seek the GroupName in Table2
             using "fldIDGroupName" and then use the Returned
             GroupName to  load it as
             List2.AddItem strGroupName

             Or ???

            Do some form of Query?

             Or

             Best way since it is in another Table?


..MoveNext
Loop


0
David
3/15/2010 3:27:21 AM
access 16762 articles. 3 followers. Follow

5 Replies
975 Views

Similar Articles

[PageSpeed] 56

Load what into where? If you are attempting to display values in a list box, 
what's wrong with simply setting their Row Source Type to Table/Query and 
the Row Source set to a SQL Statement?


-- 
Duane Hookom
MS Access MVP


"David" <NoWhere@earthlink.net> wrote in message 
news:u#byq9#wKHA.5340@TK2MSFTNGP04.phx.gbl...
> Hope I've expressed my question correctly in the title.
>
> I have two separate tables.
>
> Table1:
>
> fldIDRepairName    << AutoNumber and Primary Key
> fldRepairName        << Index, Unique
> fldIDGroupName     << Link to Foreign Table
>
> Table 2 ( I call this Foreign):
>
> fldIDGroupName      <<AutoNumber and Primary Key
> fldGroupName
>
> ==================
> If trying to load an alphabetical list of the "fldRepairName" in Table 1:
>
> and
>
> at the same time get the "fldGroupName" into a second list box??
>
> ===================
> If I loop "Table 1"
>
> Do Until .EOF
>
>     List1.AddItem !fldRepairName
>
>      >>> Do I need another procedure (function)
>              to Seek the GroupName in Table2
>             using "fldIDGroupName" and then use the Returned
>             GroupName to  load it as
>             List2.AddItem strGroupName
>
>             Or ???
>
>            Do some form of Query?
>
>             Or
>
>             Best way since it is in another Table?
>
>
> .MoveNext
> Loop
>
> 
0
Duane
3/15/2010 3:59:06 AM
Your table design doesn't seem right, if it was me I would keep it simple.

Table Repair:
RepairID           Autonumber Primary Key
Repairname
<Etc>
<Etc>

Table Group:
GroupID            Autonumber Primary Key
GroupName
RepairID            Foreign Key   From table repair
<Etc>
<Etc>

Richard


0
Utf
3/15/2010 4:44:01 AM
Thanks for both responses:

Let me try again with my question.

I normally deal with one table at a time, so getting information from that 
table is pretty straightforward.    I'm now trying to understand how you get 
information from tables where relationships exist.   For example I would 
think a "Sales" table would have a "CustomerID" as part of the table.

When you listed "Sales" in a listbox or grid or whatever, rather than just 
showing the "CustomerID" you might want to instead  show the "CustomerName" 
associated with that specific sale.

This would require that the "CustomerName" be looked up using the 
"CustomerID" when sales are listed or printed.

Question 1:

 How do you obtain the "CustomerName" from a foreign table when you get 
"Sales" from say "tblSales"?

-----------------------------------------------------------------------
Now I want something a little different.
This is what I am after:

1)
I want an alpha list of  "Sales" (Repairs in my case") in one listbox.
This is a one table query so pretty straightforward for me.

2)
I also want a second listbox that will contain a list of "Repair  Group 
Names".

When I click on the "RepairGroupName" it will show me (in another listbox or 
maybe the Alpha listbox) all  "fldRepairNames" associated or a subset to the 
"RepairGroupName".

 In order to do this, I need the "fldIDRepairGroupName" (PrimaryKey) of the 
"tblRepairGroup" included as a field in the "tblRepairNames".

This -- from my understanding -- is what database relationships are all 
about so you don't have duplicate information in multiple tables.  Makes 
perfect sense.

Question 2:

So, how do you get -- or what is the best way -- to get information from two 
tables (using SQL) where one table just has the "PrimaryKey" of the other 
table?

Thanks





"Richard" <Richard@discussions.microsoft.com> wrote in message 
news:7F891737-80FF-475C-B3BE-79B4C44AE068@microsoft.com...
> Your table design doesn't seem right, if it was me I would keep it simple.
>
> Table Repair:
> RepairID           Autonumber Primary Key
> Repairname
> <Etc>
> <Etc>
>
> Table Group:
> GroupID            Autonumber Primary Key
> GroupName
> RepairID            Foreign Key   From table repair
> <Etc>
> <Etc>
>
> Richard
>
> 


0
David
3/15/2010 11:43:28 AM
"David" <NoWhere@earthlink.net> wrote in
news:O3nj5SDxKHA.3304@TK2MSFTNGP06.phx.gbl: 

> Thanks for both responses:
> 
> Let me try again with my question.
> 
> I normally deal with one table at a time, so getting information
> from that table is pretty straightforward.    I'm now trying to
> understand how you get information from tables where relationships
> exist.   For example I would think a "Sales" table would have a
> "CustomerID" as part of the table. 
> 
> When you listed "Sales" in a listbox or grid or whatever, rather
> than just showing the "CustomerID" you might want to instead  show
> the "CustomerName" associated with that specific sale.
> 
> This would require that the "CustomerName" be looked up using the 
> "CustomerID" when sales are listed or printed.
> 
> Question 1:
> 
>  How do you obtain the "CustomerName" from a foreign table when
>  you get 
> "Sales" from say "tblSales"?


Simply use a query that joins the Sales table to the Customer table 
on CustomerID, and select customername from customers, and the other 
fields from Sales. 

> 
> -------------------------------------------------------------------
> ---- Now I want something a little different.
> This is what I am after:
> 
> 1)
> I want an alpha list of  "Sales" (Repairs in my case") in one
> listbox. This is a one table query so pretty straightforward for
> me. 
> 
> 2)
> I also want a second listbox that will contain a list of "Repair 
> Group Names".
> 
> When I click on the "RepairGroupName" it will show me (in another
> listbox or maybe the Alpha listbox) all  "fldRepairNames"
> associated or a subset to the "RepairGroupName".
> 
>  In order to do this, I need the "fldIDRepairGroupName"
>  (PrimaryKey) of the 
> "tblRepairGroup" included as a field in the "tblRepairNames".
> 
> This -- from my understanding -- is what database relationships
> are all about so you don't have duplicate information in multiple
> tables.  Makes perfect sense.
> 
> Question 2:
> 
> So, how do you get -- or what is the best way -- to get
> information from two tables (using SQL) where one table just has
> the "PrimaryKey" of the other table?
> 
> Thanks
> 
You use a select query that contains both tables joined on hte 
Primary Key of one table to the foreign key in the second instead of 
the table.
-- 
Bob Quintal

PA is y I've altered my email address.
0
Bob
3/15/2010 10:02:44 PM
Thanks Mr. Quintal:

Now that I know what I'm looking for (based on your input),
I'll see if I can construct an SQL to do it!



"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message 
news:Xns9D3CB75892456BQuintal@69.16.185.250...
> "David" <NoWhere@earthlink.net> wrote in
> news:O3nj5SDxKHA.3304@TK2MSFTNGP06.phx.gbl:
>
>> Thanks for both responses:
>>
>> Let me try again with my question.
>>
>> I normally deal with one table at a time, so getting information
>> from that table is pretty straightforward.    I'm now trying to
>> understand how you get information from tables where relationships
>> exist.   For example I would think a "Sales" table would have a
>> "CustomerID" as part of the table.
>>
>> When you listed "Sales" in a listbox or grid or whatever, rather
>> than just showing the "CustomerID" you might want to instead  show
>> the "CustomerName" associated with that specific sale.
>>
>> This would require that the "CustomerName" be looked up using the
>> "CustomerID" when sales are listed or printed.
>>
>> Question 1:
>>
>>  How do you obtain the "CustomerName" from a foreign table when
>>  you get
>> "Sales" from say "tblSales"?
>
>
> Simply use a query that joins the Sales table to the Customer table
> on CustomerID, and select customername from customers, and the other
> fields from Sales.
>
>>
>> -------------------------------------------------------------------
>> ---- Now I want something a little different.
>> This is what I am after:
>>
>> 1)
>> I want an alpha list of  "Sales" (Repairs in my case") in one
>> listbox. This is a one table query so pretty straightforward for
>> me.
>>
>> 2)
>> I also want a second listbox that will contain a list of "Repair
>> Group Names".
>>
>> When I click on the "RepairGroupName" it will show me (in another
>> listbox or maybe the Alpha listbox) all  "fldRepairNames"
>> associated or a subset to the "RepairGroupName".
>>
>>  In order to do this, I need the "fldIDRepairGroupName"
>>  (PrimaryKey) of the
>> "tblRepairGroup" included as a field in the "tblRepairNames".
>>
>> This -- from my understanding -- is what database relationships
>> are all about so you don't have duplicate information in multiple
>> tables.  Makes perfect sense.
>>
>> Question 2:
>>
>> So, how do you get -- or what is the best way -- to get
>> information from two tables (using SQL) where one table just has
>> the "PrimaryKey" of the other table?
>>
>> Thanks
>>
> You use a select query that contains both tables joined on hte
> Primary Key of one table to the foreign key in the second instead of
> the table.
> -- 
> Bob Quintal
>
> PA is y I've altered my email address. 


0
David
3/16/2010 2:48:40 AM
Reply:

Similar Artilces:

loading network card information ...error provider load failure
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01CA7D92.03F9F310 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi there, I get "loading network card information ...error provider load failure" = when I go and use systeminfo.exe . How can I fix this? Thanks, Gordon ------=_NextPart_000_000F_01CA7D92.03F9F310 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN&qu...

Linking fields in different table in same database
I have a database (Access 2007) that is used for holding data on and communicating with FE colleges. Obviously (?) each college has a number of contacts (up to 14) and these are represented in two tables – one for College Details and one for Contact Details which are linked by a one to many relationship. Most colleges have one representative on one of 3 groups, but not all colleges are represented on all groups and some not on any. The membership of a group is noted by a field in the Contact details. Getting a query to run to identify membership of groups is OK. But I can’t fig...

Scrape a website tet string into access table
I'm try to search some websites for a string which, if found, will be added to a table along with a time stamp. On some sites this works and other it doesn't. The vba code hangs up a different points in the process no matter what I try. Even something simple such as Create IE object Refresh website Close IE object Is this as IE problem or Access. There is very little help on this subject around. Hi Sorry but you will never get this to work (other than the open IE Object). This has nothing to do with access or IE. Some website "REALLY" do...

multiple charts from a single pivot table
Hi can i create several pivot charts from a single pivot table? Nope. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Yossi evenzur" wrote: > Hi > can i create several pivot charts from a single pivot table? No, but you can copy the pivot table to create another which uses the same pivot cache, so they will refresh in synch with each other, and share any grouping and calculated fields. Filtering won't be synchronised though, so you may want to either automate that with macros or use formaulas to check for bas...

Money forces incorrect currency on foreign-listed USD funds
Upon entering the symbol for USD-denominated funds listed on foreign (i.e. non-US) exchanges, Money06 mandates the use of the exchange's local currency instead of USD. For example, a UK-listed fund is forced to be entered as GBP-denominated, even though this is not correct. (If you force Money06 to report the fund in USD, it automatically changes the stock symbol's exchange locator from the correct one to "US"... causing the online updates to fail) Money03 allowed the reporting currency to be independent of the exchange's local currency. Is there a way to make Mo...

Appending data from table to table
Hi, This should be simple but I can't get it to work properly. I want to append data from one Access Db to the data in another. The tables in both are exactly the same. When I do it the importing Db is creating a new table in the Db with the data in it. I want it to append the data to the existing table thereby accumulating the data which supports a few forms. Thanks, Bob On Tue, 18 Mar 2008 15:12:57 -0400, "Bob" <bob1030@hotmail.com> wrote: >Hi, > >This should be simple but I can't get it to work properly. I want to append >data from one Ac...

Table requires Custom Linking to Excel
I have a table in Word 2000 with various columns. Each row represents one order for a meal package. One of the columns indicates one of three baked pies to order (apple, cherry or pumpkin). Another column indicates whether or not the order has been paid or unpaid. Is there a way to put a total number for each of the pie types ordered into an Excel worksheet? Similarly, would there be a way to analyze all of the rows, and for all rows with an unpaid status, multiply this number by the cost for each meal package, and display this result in an Excel worksheet as money due? Thanks...

OWA (Not Loading)
Does anyone know of any issues with Satellite Service and the right side of OWA not loading. The left side loads but the user cannot see anything on the right. OWA works from any other connection. Thanks, KG ...

SOS,how to load a invalid or damaged .gif file to create Image Object.
hi everyone. my question is this:how to load a .gif image file that is invalid or damaged image format? Notice,I want to load the invalid damaged image file to operate it. my code is below,but all of they are not correct. -------- 1.Image.FromFile(fileName);load from a file directly,it throws a OutOfMemoryException.In msdn,it's mean the file is a invalid image format. 2.Image.FromStream(stream);load from stream,it throws a ArgumentException. -------- ps:IE and Google Picasa can load and display the damaged .gif file correct. Or there is any other methods to operate the i...

Endnotes in a table
Hi. (Word 2007 [dk]) In my document I have a lot of endnotes. Most of the notes are hyperlinks to other documents. Is it possible to format the endnotes as a table where column 1 is the endnotenumber, column 2 the endnote (words) and column 3 is open for me to enter the hyperlink ? Regards Peter An actual table might be difficult, but you could certainly add tab stops to the Endnote Text style. Create a new endnote (which inserts the endnote reference number), press Tab to get to the words and Tab again for the hyperlink. Naturally, this would work only if both verbiage and l...

Error loading CRM 3.0 Client with Outlook
I receive error message "An error occurred loading Microsoft CRM Functionality" when starting Outlook with CRM 3.0 client installed. I have uninstalled CRM, Office, and cleaned the register using Register Mechanic. I am now getting the same error with an event error "A problem occurred initializing Microsoft CRM COM interopt". Anyone have any ideas? Thanks. I have the exact same issue has anyone found a solution to this? I am running all avialable updates on both Office 2003 and XP Pro, I DO NOT have the Business Contact Manager for Outlook installed. "rtop...

loading web application project suddonly takes 5 mintues and says "Contacting server to open Web project.." wile it takes 5 minutes to load. how to fix? i tried reregistering asp.net and iisreset. no
loading web application project suddonly takes 5 mintues and says "Contacting server to open Web project.." wile it takes 5 minutes to load. how to fix? i tried reregistering asp.net and iisreset. no progres. ...

Converting permanent table to Temp table and where clause
I have a Permanent table that I am changing to a Temp table. It is used in a bunch of places in my code and there is a column "UID" that is always going to be the same whereas it wasn't when it was a permanent table. So I have thinks such as: SELECT... WHERE UID = @UID or DELETE ... WHERE EID = @EID and UID = @UID I was debating taking out the places where it says "UID = @UID" since it will always be the same. Does leaving it in change the plan and would it affect the performance? Thanks, Tom thsad I am not sure that understood...

count row in table
I have table with 20 row I want to make serial for this row with out using autonumber Notes: I don't want use autonumber the data alredy exists in table i don't want use query I want just table example I want to add field name (Serial) give each row in the table (number) begin with 1 end by the row in the table can I do that just add a field to the table, name it whatever you want, set the data type to Number, and save the table. then open the table in Datasheet view, and enter the numbers 1 thru 20 in the records. hth "a" <youb@hotmail.com> wrote in message ...

how to: if a value is entered in one row, then associated data is cummulatively added to a 'total' on another sheet ?
Excel Version : 12.2.3 OS X 10.6.2 Intel Hi =97 I am very new to Excel, and am learning a lot, but have found it difficult to work out a new problem. I am trying to nut out a formula to do the following (I have searched all over, but realise I don't really know what my search terms should be). For arguments sake, I have two worksheets, A and B. Worksheet A is used as a meta-summary of what will happen elsewhere in the Workbook. Worksheet B is where the raw data is entered (as it will in C, D, E, etc, in the future). I have created a drop down list of categories for use in Wo...

Excel help needs an article answering, "What is a pivot table?"
If you type "Pivot Table" into the help search field in Excel there should be an article near the top of the search results that answers the question "What is a pivot table?" ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.co...

Pivot Table #37
I have a list of names who have done a particular process and the time taken for that process . The same persons might have done another process. I need to add the time taken by a particular person for both the process together. Eg. A has taken 10 min to do process 1, he has also taken 15 min to do process 2. Hence on a day he has spent 10+15 which is 25 min. I'm Using office 2000. Let's say you have a column of data for NAME, PROCESS and TIME TAKEN Select the cells of interest Data -> Pivot Table Microsoft Office Excel list Pivot Table Select NEXT Select NEXT I prefer New...

What do I load ?
Hello. I'm trying to develop a web comsuming client using VS.NET 2003(VB), .NET Framework 1.1.4322, ASP.NET 1.1.4322, WSE2.0 on a WinXP Pro Sp2 comuter. If i comsume this web service: https://xatanet.net/xatanetwebservice/tripexportservice.asmx and do this operation: RetrieveArchivedTrips using: Dim strDate As Date = "2006-04-01" Dim endDate As Date = "2006-04-28" proxy.RetrieveArchivedTrips(strDate, endDate) What do I load when i use the following statements in my program: Dim doc as XmlDocument = new XmlDocument() doc.Load() Any...

Excel 2007 and refresh pivot table methodology
Hello, I have moved from excel 2003 to 2007 and come with a question regarding the way pivot tables refresh is handled. I have several pivot tables in different sheet of the same workbook and all have the same data input range . The question is that when I ask to refresh ONE pivot table excel will refresh ALL pivot tables in all sheets having the SAME data range source. It was not the case in 2003 and I'd like to ONLY refresh one specific table , is there a possibility to do that ? Thanks, Bernard ...

Debug image fails to load.
Hi, I created an OS design with KITL, Kernel debugger enabled in Debug mode. But when i try to load the image to the CEPC it fails to boot. I am attaching the log here. ------ SERIAL PORT LOG starts ----------- Microsoft Windows CE Ethernet Bootloader Common Library Version 1.0 Built Dec 9 2002 18:35:13 Copyright (c) 2000-2001 Microsoft Corporation Microsoft Windows CE Ethernet Bootloader 3.3 for CE/PC (Feb 6 2003) Boot Args @ 0x1F136 and ucLoaderFlags is 1 PCI Device Configurations (3 PCI bus(es) present)... ======================================================== Bus, Devi...

linking data to an Access table
I am trying to set up a routine where I can export all my shape-info to an Access table. It seems to be a little "unstable"... sometimes I have to pick an ODBC-File Data Source - and sometimes I dont have to. My field definitions also sometimes "reset" to a very limited range of fields.... Is there an "authorized" way of doing this - so that I get a good solution - that I can use again and again? I guess the question is "how are you exporting"? Are you using the wizard or are you using real code? (I guess I hinted at what I think the answer is) Al...

Excel name sorted to Pub table
I have to publish a weekly bulletin that includes a list of about 100 names, give or take. It is currently visually presented as three columns of about 35 names each, as parallel balanced newspaper columns. There's not much movement in the name list, say about 5-10% in a given week. Our editor is currently keeping this in a table frame but it's not in any sort order and is a mess to update and keep visually pleasing. I set up an Excel sheet for her and imported the last and first names into two columns, and then for printing I concatenated those two fields into one full-name ...

Sorting Grouped Dates in Pivot Table
Having problems sorting grouped dates in a pivot table report. I have a column filled with dates. I run pivot table and sort the dates in ascending order. Everything's fine. Then I group the dates by 7 days (since there's no automatic weekly grouping.) The resulting pivot table have a weird sorting: 3/29/2004 - 4/4/2004 4/12/2004 - 4/18/2004 4/19/2004 - 4/25/2004 4/26/2004 - 5/2/2004 4/5/2004 - 4/11/2004 5/3/2004 - 5/9/2004 I have made sure that all the other visible fields (page and column) have no sorting order. Why is this happening? gio The date range is a text string, and 4...

pivot table in data source order
My pivot table report automatically sorts the data, rather than keeping it in data source order, which is what I want. I click on the "Manual" option in the advanced section to no avail. The option button that says "Data source order" is greyed out so I cannot select that option. I am using Excel 2002 AFAIK, the Data source option is only enabled for OLAP cubes. For pivot tables built from Excel data, the items are listed in Ascending order when the pivot table is created. If you set sort to Manual, you can drag the items to any position in the list. You could als...

Dart Scoring table
I've made an auto dart scoring sheet. In column A I have the dart score input, in column B it auto subtracts that score from the set amount. The problem is if I use say 301 as the start score in column A column B shows 301 thru all of row B. Is there a way to hide the 301 in column B until a score is posted in column A? "LCBO" <LCBO@discussions.microsoft.com> wrote in message news:442CA8D9-EB08-4A84-9BD9-F1E7B104B641@microsoft.com... > I've made an auto dart scoring sheet. In column A I have the dart score > input, in column B it auto subtracts tha...