Re: Access 97 - Multiple Table Query Question

From: Jeff Homan (jeff_at_jcswebhosting.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 13:04:08 -0500

Is there anyway to do this if there's a limit of 6 fields? I don't
know a ton about databases, but the only way for us to get this to
work is to have all the data in one query results table to migrate
this all to another database (where it is then put into multiple
tables (essentially rebuilt)).

Basically the problem that I'm having now, is that I've got a table
that has 6 fields in a 1 to many relationship with a part number
table.

Is there a way to use an aggregate function for this to accomplish the
task. Such as this:

VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
WHERE(Applications.[Part Number] = Inventory.[Part Number])

There doesn't seem to be a "Second" function like there is a "First" -
What would be the best way to pull that information?

Here's the Product Table

Part Number Description ....
-------------------------------------------------
ABC123 Test Part
ABC124 Test Part 2

Here's the Applications Table

Part Number AppCode Application
------------------------------------------------------------------------
ABC123 ZZZ Test APP
ABC123 ZZZ Test App 2
(to a max of 6 of these entries)

Is there any way to make a query that will result in this:

Part Number - Description - AppCode1 - Application1 - AppCode2 -
Application2 - AppCode3 - Application3... (up to 6)

Thank you for your help!
Jeff

On Sat, 9 Oct 2004 23:58:12 +0800, "Allen Browne"
<AllenBrowne@SeeSig.Invalid> wrote:

>Hi Jeff
>
>Nothing that wide is going to work. It will be too wide. Rethink the
>strategy.
>
>I'm not clear what kind of software you are attempting to load this into on
>the other end, but the most logical approach to me would be a simple query
>with the 2 tables linked, as the source for a report. If you want it on
>paper, you could even start a new page for each vehicle.
>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia.
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>
>"Jeff Homan" <jeff@jcswebhosting.com> wrote in message
>news:7k0gm01tk4dvn6vk6g7rdom3gdhcqpgodo@4ax.com...
>> Allen - Once again, thank you for the reply and suggestions.
>>
>> To give you more information about what I'm trying to do is migrate a
>> current inventory list to an online store. The best way that I can see
>> to do this is run a query on the existing database with all
>> information in a single query-result-table, and then I can add that to
>> the online store.... The issues that have come up have been how to
>> pull the info from the inventory table and 2 one to many tables all in
>> one query. Allen, you've helped me with the 1st one, this last
>> applications table is the next place I'm stumped.
>>
>> But, I spoke with the company I'm tinkering on this for, and they said
>> that we could use a max of 25 applications. Is there a way to use an
>> aggregate function for this to accomplish the task. Such as this:
>>
>> VehicleApp1: (SELECT First(Vehicle, Description) FROM Applications
>> WHERE(Applications.[Part Number] = Inventory.[Part Number])
>>
>> VehicleApp2: (SELECT Second(Vehicle, Description) FROM Applications
>> WHERE(Applications.[Part Number] = Inventory.[Part Number])
>>
>> There doesn't seem to be a "Second" function like there is a "First" -
>> What would be the best way to pull that information?
>>
>> Thank you,
>> Jeff
>>
>> On Sat, 9 Oct 2004 11:54:48 +0800, in microsoft.public.access.queries
>> you wrote:
>>
>>>Hmm. That would be a rather unweildy interface, and could potentially get
>>>wider than 255 fields or 2000 characters quite easily. Impractical.
>>>
>>>In the Application table, one Vehicle could appear many times? Seems to me
>>>you need a Vehicle table, so that Application becomes a junction table
>>>between Parts and Vehicles. You could then create a report that displays a
>>>more conventional layout, e.g. grouping by Vehicle, and listing the parts
>>>under it.
>