Loading Table with Associated Foreign Table

[David]

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

 

[Duane Hookom]

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?

 

[Richard]

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

 

[David]

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

 

[Bob Quintal]

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.

 

[David]

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!