Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create New Access Table from Primary Keys of two other tables 1

Status
Not open for further replies.

iSRS

IS-IT--Management
Sep 27, 2013
3
US
Hello,

New (returning after years away and wasn't able to reset my password) here and couldn't find this anywhere, so figured I'd start this thread. (Originally posted in a more generic forum)

I am working on an Access 2010 database I created. I am looking to do the following.

Create a table from the primary keys from two different tables, along with some other info that would be unique to the key+key combo

Tables I have:
tbl_Resources
[PK]ID (AutoNumber)
First (Text)
Last (Text)
Role (Text)
etc.

tbl_CouseList
[PK]Course Number (Text - this is because there are predefined course numbers that are 6 digits and could start with zero)
Course Name (Text)
Course Type (Text)
Required Course (Yes/No)

What I need:
A table that will take every Resource ID, and assign every Course Number for each Resource ID, and then add the Required Course from tbl_CouseList, then additional, new fields:
Status ({blank}, Complete, Scheduled, TBS)
Company Course (Yes/No - This is identifying if it was an internal course or third party/outside course)

Is this something that can be done? What are the steps?

Example:
Code:
ID     CourseNumber     Required Course     Status        CompanyCourse
1      012345           -1                  Complete      -1
1      012346           -1                  Scheduled     -1
2      012345           -1                  Scheduled     -1
2      012346           -1                  Scheduled     -1
Thanks for your help, folks!
 
The new table can be made through the UI and is probably the easiest... I often copy and paste from one design view to another to get columns and add more.

Beyond that you need a query to pull the data. Sounds like you want all possible combinations from both tables. This is just having two tables in a query without a join (sometimes called a cross join).

Beyond that it is simply making the Select query an append query (Inert Into in SQL).
 
Thanks, lameid! Figured it was an easy thing to do, but it was that first step and I was over thinking.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top