Functions for Crosstab or Alternative Approach?

[Rita Palazzi]

Windows XP Professional
Office 2000

I have a table where shipment activity is posted. If a shipment is held
by US Customs, there are up to 5 different reasons as to why. Only
unique field is the time the reason is determined.

A simple example of the table would be as follows:

ShpmtID Action Reason Date/Time
101 HELD EXAM 07/18/05 12:01
101 HELD CUSTR 07/18/05 01:15
101 HELD CARGO 07/18/05 01:20
201 HELD EXAM 07/18/05 12:01


What I need is to combine all reasons for shipment #101 into one field.
I can count the number of reasons per shipment easily in a crosstab.
Is there any way to concatenate the reasons? Time of reason
determination is unimportant.

My end result would be:

ShpmtID Action Reasons
101 HELD EXAMCUSTRCARGO
201 HELD EXAM


Any help would be greatly appreciated!

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

 

[Michel Walsh]

Hi,

You can in a temporary table.

1- Delete the temporary table if it exists.

2- Create the temporary table like:

SELECT DISTINCT ShpmtID, iif(false, "" , null) As concat INTO temp FROM
myTable



3- Update the data

UPDATE temp INNER JOIN myTable ON temp.concat=myTable.ShpmtID
SET concat=(concat + ", ") & reason
ORDER BY DateTime



the result is then in the temp table.




Hoping it may help,
Vanderghast, Access MVP

 

[Rita Palazzi]

Can't get the update to work. I'm very very new to SQL and am unsure of
correct syntax. I have tried both statements below in one query which
results in "Syntax Error in FROM clause". I've tried two individual
queries and the update has 0 results.

Any ideas?
Thanks
Rita

 

[Michel Walsh]

Hi,


You are using Jet? or MS SQL Server? I assumed you used Jet.


The "SELECT DISTINCT ... INTO temp FROM myTable" generates a table
called temp, two fields, ShpmtID and concat, with the second field with
NULLs in each record, while the first field get each values from ShpmtID,
but listed just once. Also, replace myTable with your real table name.

You have to have that "temp" table right before executing the update
query.




Vanderghast, Access MVP

 

[Rita Palazzi]

PLEASE forgive my lack of education. I'm on a "learn as you go"
mission. I have the table in Access and and trying to write a query in
Access. I have MS SQL Server installed on my machine and could probably
figure out how to access the table with it, but was trying to do
everything within Access. Is it even possible to do it in Access?

thanks again for you help!
Rita

 

[Michel Walsh]

Hi,


So you have Access 2000 or more recent as front end, and MS SQL Server
2000 as back end, and you use a "project" (or a dot-adp file)? MS SQL
Server does not use "iif", and the instructions I supplied do NOT apply to
MS SQL Server. Jet and MS SQL Server have a lot of difference, in the
details, so it is important to know what is the database engine, exactly.

I will come back to you with steps to do it in MS SQL Server.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


What I suspected was confirmed by experimentation: MS SQL Server does
not UPDATE through the join the same way JET does. (In fact, I updated
through the view). Only the last modification is kept, rather than each
successive ones, as in Jet. So, in short, the technique illustrated before
CANNOT be use at all in MS SQL Server. Sorry.


Vanderghast, Access MVP

 

[Rita Palazzi]

Thanks for the attempt. I have no clue what JET does. All my data is in
an Access table and I'm trying to write the query in Access. I was
hoping to do a crosstab query that would just "concatenate" (into one
field) all reason codes for one shipment.

Sorry that I'm so functionally illiterate!! I'll try to do some
research on JET. I'll also try to get some "continuing education" classes!!

Thanks again,
Rita