Linking Access to external Application

[cegbuna]

I successfully linked access to an external application and the tables look accurate, but I am having a couple of problems running queries for the linked tables.

Example of one of the linked tables (about 5 linked Tables)

<table width="338" border="0" cellpadding="0" cellspacing="0" height="180"><col style="mso-width-source:userset;mso-width-alt:731;width:15pt" width="20"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:15pt" width="20" height="20">ID</td> <td class="xl67" style="border-left:none;width:50pt" width="66">Tool Type</td> <td class="xl74" style="border-left:none;width:57pt" width="76">Tool Maker</td> <td class="xl68" style="width:58pt" width="77">Install Date</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" height="20">1</td> <td class="xl65" style="border-top:none;border-left:none">Pump</td> <td class="xl75" style="border-top:none;border-left:none">Company 1</td> <td class="xl70" style="border-top:none">Date 1 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" height="20">2</td> <td class="xl65" style="border-top:none;border-left:none">Motor</td> <td class="xl75" style="border-top:none;border-left:none">Company 2</td> <td class="xl70" style="border-top:none">Date 1 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" height="20">3</td> <td class="xl65" style="border-top:none;border-left:none">Pump</td> <td class="xl75" style="border-top:none;border-left:none">Company 3</td> <td class="xl70" style="border-top:none">Date 2</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl71" style="height:15.75pt;border-top:none" height="21">4</td> <td class="xl72" style="border-top:none;border-left:none">Motor</td> <td class="xl76" style="border-top:none;border-left:none">Company 4</td> <td class="xl73" style="border-top:none">Date 2</td> </tr> </tbody></table>

Problem 2: How do I create a make table query to have "Pump" and "Motor" on individual Columns and their corresponding Makers under them?

Problem 1: When I run a simple query to return all tools installed based on the ID in the tables (regardless of the install date), it returns multiple repeated data for a single record with only one install date. How do I fix this so that I am pulling the data accurately?

I appreciate any help I can get and I apologize if my posting is vague.

 

[sissajones]

Use something like the following:

Pump: (IIF([Tool Type]="Pump", [Tool Market],""))

then do the same for the motor column - hope this makes sense.

 

[cegbuna]

Thanks, it works but It leaves space between data like below. Each line or row should contain a Pump, Motor and Installation data information.

Is there a way to fix this?

Thanks again for your previous response.

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption></caption> <thead> <tr> <th bgcolor="#c0c0c0">ID</th> <th bgcolor="#c0c0c0">Install Date</th> <th bgcolor="#c0c0c0">Pump</th> <th bgcolor="#c0c0c0">Motor</th> </tr> </thead> <tbody> <tr valign="TOP"> <td align="RIGHT">1</td> <td>5/1/2011</td> <td>Company 1</td> <td>
</td> </tr> <tr valign="TOP"> <td align="RIGHT">2</td> <td>5/1/2011</td> <td>
</td> <td>Company 2</td> </tr> <tr valign="TOP"> <td align="RIGHT">3</td> <td>6/1/2011</td> <td>Company 3</td> <td>
</td> </tr> <tr valign="TOP"> <td align="RIGHT">4</td> <td>6/1/2011</td> <td>
</td> <td>Company 4</td> </tr> </tbody> <tfoot></tfoot> </table>

 

[sissajones]

OK - I see what you're asking for but if you look at your results above - ID 1 refers to a particular record - that record is unique based on the ID - it only had a Pump from Company 1 - it did not contain a Motor from Company 2

Are you simply looking for a suppliers list? In other words we can get this Pump from these companies and this Motor from these companies? After I know that I can help more.