Create Duplicate Rows

[kshope25]

I posted the other day and received helpful feedback, however I still cant get the query to do what I need it to do.

I have a table with the following columns (Pick Up Route, Intermediate Route, Intermediate Route 2, Delivery Route)

The PU Route and Delivery Route will ALWAYS have a route listed in the column/s. The IntRte and IntRte 2 may or may not have routes listed in them depending on how the freight is routed.

P/U Rte............IntRte................IntRte2................DelRte
MN1-601.......... ............. ...................................MN8-443
MN3-335.........MN8-888........... ...........................MN7-773
MN4-443.........MN7-413..............MN9-919.............MN1-111


I need my query to create a seperate column (called Main Route) that will list each of the routes in the string of data as the main route. So I would have as few as two rows (in the first string of data above) or as many as 4 rows (in the last example above). What i learned the other day is that the table is not organized properly; however, I cant do anything about that, as that is how I receive the data. The table I receive has hundreds of rows, so any manual entering is not plausable. Also, i'm just learning access, so if you can be as specific as possible, I would greatly appreciate it! Thanks in advance.

Table would look as follows
P/U Rte............IntRte................IntRte2................DelRte.......Main Rte
MN1-601.......... ............. ..............................MN8-443........MN1-601
MN1-601.......... ............. ..............................MN8-443.......MN8-443
MN3-335..........MN8-888........... ......................MN7-773.......MN3-335
MN3-335..........MN8-888........... ......................MN7-773.......MN8-888
MN3-335..........MN8-888........... ......................MN7-773.......MN7-773
MN4-443.........MN7-413............MN9-919........MN1-111.......MN4-443
MN4-443.........MN7-413............MN9-919.........MN1-111.......MN7-413
MN4-443.........MN7-413............MN9-919.........MN1-111.......MN9-919
MN4-443.........MN7-413............MN9-919.........MN1-111........MN1-111

 

[jdraw]

Please post a jpg/gif of your tables and relationships.

What conditions require that an intermediate route(s) get added?

 

[kshope25]

The intermediate route/s are already added (if necessary) by the time i get the table. Someone else does that manually

There are intermediate routes depending on how many crossdocks the product travels through. If it travels through one cross dock, there is no intermediate route (just a p/u and del route). If it travels through 2 cross docks there is a PU, Int, and del route). If it travels through 3 cross docks, there is a PU, Int, Int2 and del. route). All of this information is in one table.

 

[jdraw]

I do not understand what you are trying to do, nor do I find your samples clear.
In your attached sample, I don't see how this works. I see Names(clients?) and route info, but nothing obvious that identifies Main Rte??

Please describe with an example.

 

[kshope25]

Sorry for being so unclear. I'm not usually the guy who works on this stuff. Our main guy is out for a while. See the example attached here, and let me know if that makes more sense?

 

[plog]

There's no silver bullet query that will do what you want. I can see a hacky way that involves union queries, but that would be more effort than properly structuring your data as suggested in your previous thread (http://www.access-programmers.co.uk/forums/showthread.php?t=222518).

Just because you receive your data in one way doesn't mean you can't easily set up a way to properly extract it into the tables that it should be in. Once its in the proper structure, a query to get the data you want becomes trivial.

 

[kshope25]

Ok, i'm going to give the table structure you originally suggested another go. I tried it first, and it seem like I got right back to where I was some how. As I said up front, Access is not my strongpoint for sure. I appreciate your continued assistance with this issue of mine. You'be been a great help.

 

[georgedwilkinson]

I think you're trying to do this:
http://allenbrowne.com/func-concat.html

The first time I tried this, it took forever. I don't know that I'm any better at it now than I was.

HTH