Query To Combine Two (or more) Records into one row

[stildawn]

Hi All

Not sure if this needs to go in Forms or Queries, I assume queries.

I have the below records (simplified):

Carrier	FCL Type	FCL Cost
Shipco	20GP	300
Shipco	40GP	400
Boatsrus	20GP	150

Is there a way I can create a query to show in a listbox on a form that combines the first two records into one line, that would end up looking like this:

Filtered by: Carrier = Shipco
Result:

Carrier	FCL Type	FCL Cost	FCL Type	FCL Cost
Shipco	20GP	300	40GP	400

As you can see the above just combines the two Shipco records and lists all types/costs in one line.

Is there a way to do this? Considering that there may be up to around 10 different FCL Types / Costs (but usually 2 as above) so the amount of columns could be from 5 to 20 depending on result of query.

Thanks

[ranman256]

sql doesn't work that way. It wants to do the top table.

[June7]

Can sort of be done with 2 CROSSTAB queries then join the 2 CROSSTAB. Need a unique identifier field - autonumber should serve.

TRANSFORM Max(Data.FCLType) AS MaxOfFCLType
SELECT Data.Carrier
FROM Data
GROUP BY Data.Carrier
PIVOT "Type" & DCount("*","Data","Carrier='" & [Carrier] & "' AND ID<" & [ID])+1;

TRANSFORM Max(Data.FCLCost) AS MaxOfFCLCost
SELECT Data.Carrier
FROM Data
GROUP BY Data.Carrier
PIVOT "Cost" & DCount("*","Data","Carrier='" & [Carrier] & "' AND ID<" & [ID])+1;

SELECT Query1.*, Query2.*
FROM Query2 INNER JOIN Query1 ON Query2.Carrier = Query1.Carrier;

Other options:

http://allenbrowne.com/func-concat.html

VBA writes records to a 'temp' table

[pbaldy]

This could be an option, adapted to return 2 fields:

http://allenbrowne.com/func-concat.html

You wouldn't get discreet columns though. For that you could probably use subqueries, but it could get messy.

[stildawn]

Thanks all, food for thought.

It might be a bit too complicated to try and combine to be honest.

Here is the run down of what I am trying to achieve:

• Create a database to store all our FRT costs, the main aim is to massively reduce data entry into the database as currently lots of staff dedicate too much time to keeping the old system updated
• With the aim to reduce data entry, I am going to make a excel template file that can push in hundreds of records at once, with data entry done by manipulating excel files provided by our suppliers etc into this excel template into access.
• The reason I have FCL Type as a separate record each time is becuase there can be around 10+ different FCL types, so I thought I couldnt have more than one type per record as I wont know beforehand how many fields (FCL Types) to include in the main table in Access
• So my question is, instead of trying to combine two records into 1 line as originally asked above, is there perhaps a better way to imput the data into Access in the first place?
  
  • Is there a way so that if a user adds a column for each FCL Type in the Excel template, that Access expands the table fields automatically? And this new field pulls through to all the forms/queries etc?
  • Or does all the fields have to be set at the start?
  • For example, original data in access could be:

Carrier	20GP Cost	40GP Cost
Shipco	200	400

• 
  
  • And this table above pulls through on all forms etc as is.
  • But then someone adds in some costs as below:

Carrier	20GP Cost	40GP Cost	40HC Cost
Shipco	200	400	450

• 
  
  • So they add another FCL type and its cost, can Access dynamically expand to include that?

I'm not the best at Access, much better at Excel/VBA, however perhaps the above is possible using two linked tables? The problem is the records above are simplified examples, in real data they have Origin Port / Destination Port as well as Carrier as defining features. Can two linked tables work as below:

Table 1:

Origin	Destination	Carrier
NY	London	Shipco

And that one records (port pairs and carrier combined) links to this table:

Table 2:

20GP Cost	40GP Cost	40HC Cost
200	400	450

And if that is possible, how then would you create a excel template that would easily update all of them? (By update, I mean add new records with new validity dates)

Cheers

[NoellaG]

Relational databases don't work that way. Having a quick look at your example I would think you can have:
a table for the carriers : fields ID (PK); CarrierName; other carrier info like address, phone, ...
a table for the Routes: ID (PK); Carrier (= ID from the carrier table); origin; destination (in fact origin and destination can come from a seperate cities table)
a table for the possible costs types: ID (PK); code (20GP, 40GP, …); description
a table for the costs: ID (PK); carrierID; RouteID; TypeID; amount

The data in these tables can be managed by access forms or through import queries that import the excel data into the correct tables

Once you have those, you can build a crosstab query/report to view the data in the way you want. Mind you, crosstab queries are not updateble

[June7]

Advise not to use Excel for data entry. This would require a lot of VBA, nothing easy about it. Use Access forms bound to tables.

CROSSTAB query is dynamic. But be aware a query is limited to 255 fields just as a table is. Building a stable report based on CROSSTAB is not easy.

Tables can be linked on multiple fields - compound JOIN.

Have you studied any tutorials on relational database concepts and/or Access?