Extract data from 1 table according to another

[hendriksmit]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Easy question, i think.<o></o>
I have 2 tables. One with data containing file names and paths for old and new projects. Another that contains a list of current job numbers. <o></o>
The file paths contain the project number.<o></o>
<o> </o>
What i want to do is create a new table that extract all the file names and paths that contain the project numbers in the project number table.<o></o>
How do i do this?<o></o>

Thanks,
Hendrik

 

[Joe4]

Welcome to the Board!

Firstly, you probably do not want to extract the results to a table, a query should usually suffice (and will maintain data integrity and the dynamic nature of the database if you do so).

The first question is, do you have the appropriate fields in each table to link the two tables together in a query? If not, you may need to create an intermediate query to extract what you need to link out of a larger fields using text functions like LEFT, RIGHT, MID, and INSTR.

If you need helping accomplishing all this, it would be very helpful if you could post a small sample of the data you have in each table, and what you would like you desired output to look like.

 

[hendriksmit]

Thanks Joe.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o>New to this but have some basics. hope this will help to explain.</o>
<o></o>
Table 1 "Files" with file name and path has got field names: ID (auto number), FName, FPath, DateCreated.<o></o>
<o> </o>
<TABLE style="BACKGROUND: white; mso-cellspacing: 0cm; mso-yfti-tbllook: 1184" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0.75pt" colSpan=4>

Files<o></o>​

</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

ID<o></o>​

</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

FName<o></o>​

</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

FPath<o></o>​

</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

DateCreated<o></o>​

</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

164370<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>2010_AA_AB_AL_EL_A_0104-C08.pdf<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>M:\06019 North Bristol Hospitals P21\06019 Issued\2010 Elevations\<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

02/09/2009 12:15:43<o></o>​

</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

164371<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>2010_AA_AB_AL_EL_A_0201-C04.pdf<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>M:\06019 North Bristol Hospitals P21\06019 Issued\2010 Elevations\<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

02/09/2009 12:15:43<o></o>​

</TD></TR><TR style="mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

164372<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>2010_AA_AB_AL_EL_A_0202-C02.dwg<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>M:\06019 North Bristol Hospitals P21\06019 Issued\2010 Elevations\<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

02/09/2009 12:15:43<o></o>​

</TD></TR><TR style="mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

164373<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>2010_AA_AB_AL_EL_A_0202-C02.pdf<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>M:\06019 North Bristol Hospitals P21\06019 Issued\2010 Elevations\<o></o>
</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

02/09/2009 12:15:43<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
<o> </o>
Table 2 "tblCurrentProjects" with current projects has got field names: ID (auto number), PrjNumber.<o></o>
<o> </o>
<TABLE style="BACKGROUND: white; mso-cellspacing: 0cm; mso-yfti-tbllook: 1184" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0.75pt" colSpan=2>

tblCurrentProjects<o></o>​

</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

ID<o></o>​

</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: silver; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset black .75pt">

PrjNumber<o></o>​

</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

2<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>08048<o></o>
</TD></TR><TR style="mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>

3<o></o>​

</TD><TD style="BORDER-BOTTOM: #d0d7e5 1pt inset; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-BOTTOM: 0.75pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.75pt; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>06019<o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
So it should look at file path for the project number anywhere in the path and then list the file and the associated path for all the project numbers in table 2.
<o> </o>
Thanks again.<o></o>

 

[Joe4]

If the Project number can occur anywhere in the File Path, it is going to make things a bit harder.

If the projext number will always appear in the same spot in the file path, then we can use Text functions to extract it.

Is there any "hard and fast" rule where the project number will occur in the file path?

 

[hendriksmit]

Joe,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
After studying the data it looks like the number appears just after the “M:\” but it can be 4 or 5 digits long like the example below.<o></o>
So i think we will cover 99% of all the situations with this assumption.<o></o>
<o> </o>
M:\0276 Grove\01 LIVE\CODEBOOK DATABASE\archive\2005-01-13 reports\<o></o>
M:\05071 Cashel Phase 2\to burn\I Intranet\projectThumb\<o></o>
<o> </o>
Hendrik.<o></o>

 

[Joe4]

So, then in a query, I think we should be able to pull Project Number out of the File Path with a calcuated field like this:

PrjNum: TRIM(MID([FPath],3,5))

So, if you then add all the other fields you need from Table 1 to this query, you can now create a new query where you join this query you just made to Table2 on the Project Number fields.

Does this give you what you want?

 

[hendriksmit]

Joe,

Exactly what i need. Just changed the 3 to 4 and it was perfect.
Also made the other query and it worked.
Thanks for your help.

Kind regards,
Hendrik

 

[Joe4]

Ah yes, forgot to count the colon, then it would start at 4 not 3. Sorry about that.

Glad we got it to all work out.