4 a Query? See City/States pairings >> 5-10 new records

[Kreiz]

Hello,

Learned a great deal of Excel from a similar forum and now have a larger project that everyone mentioned I probably will want Access for.

Hourssssss of training videos later I have a familiarization of MS Access

Is this going to need a long query?

Situation: Have a frequent report created at work of purchases from different city/state to different city/state pairings. The rate for each shipment varies but we would like to have a method procedure for MS to recognize the lane ""Jacksonville, FL to Charleston, SC"" and then create a predetermined amount of records where the origin and destination has change but nothing else about the load information has(the weight, pickup and delivery date, etc)

In theory this will make the first order(top one) seem as if this is a lot more from the table of pre-made creatives(the 0000 rows) lanes
LOAD# CUSTOMER SHIP DELIV SHIP CITY S ST CONS CIT C ST CUST$CARR TYPEWGHT
4654 BILLY BOB'S PAPER 8/14/2011 8/15/2011 JACKSONVILLE FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL COLUMBIA SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL CHARLOTTE NC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/11/2011 8/13/2011 JACKSONVILLE FL AIKEN SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL LUMBERTON NC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/15/2011 8/13/2011 ORLANDO FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/15/2011 8/13/2011 TAMPA FL CHARLESTON SC 0.00 0.00 otr 440


0000 BILLY BOB'S PAPER 8/14/2011 8/13/2011 GAINESVILLE FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/14/2011 8/13/2011 VADOSTA GA CHARLESTON SC 0.00 0.00 otr 440


I Have made a few tables in Access and have A LOT of data uploaded just need to know how to manipulate this at the moment. Any ideas area MUCH appreciated.

Thank you

[NTC]

Sorry don't really understand your post.

Looking at the sample lines of data you provided - it is not clear what it is that you begin with - vs - what you need to end up with.....

[rpeare]

I don't understand the question either, it seems like you want to be able to add stops to a longer haul and add drop offs or pick ups from that haul. but I don't really understand the question either.

[Kreiz]

4654 BILLY BOB'S PAPER 8/14/2011 8/15/2011 JACKSONVILLE FL CHARLESTON SC 0.00 0.00 otr 440

The above row is the actual shipment we have. We need to post this one load coming out of Orlando, Tampa, and many other cities for origin, and many other cities for Destination. Each row in our raw data is 1 shipment and we are wanting to make 1 shipment look as if we have 5-10.


The below rows are the lanes we are wanting to have a program create for us.
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL COLUMBIA SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL CHARLOTTE NC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/11/2011 8/13/2011 JACKSONVILLE FL AIKEN SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/12/2011 8/13/2011 JACKSONVILLE FL LUMBERTON NC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/15/2011 8/13/2011 ORLANDO FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/15/2011 8/13/2011 TAMPA FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/14/2011 8/13/2011 GAINESVILLE FL CHARLESTON SC 0.00 0.00 otr 440
0000 BILLY BOB'S PAPER 8/14/2011 8/13/2011 VADOSTA GA CHARLESTON SC 0.00 0.00 otr 440

[Kreiz]

I have created a Lane's table, including all of our ACTUAL lanes, Ship City, S State, Deliver City, D State, and Lane ID(primary key)
As well a table with our creative lanes, Ship City, S State, Deliver City, D State, Lane ID, Creative ID(Primary Key) and I have a query pull all of our active shipments and their data. Tried to setup "joins" or relationships using Lane and the Creative tables linking the Lane ID but am getting ridiculous results. Let me know what you need to see and will do what i can to accommodate.

[rpeare]

I am afraid I still do not understand what you are trying to do.

I assume by lane you mean a shipping route (one starting city to one ending city) that is explained by this line:

4654 BILLY BOB'S PAPER 8/14/2011 8/15/2011 JACKSONVILLE FL CHARLESTON SC 0.00 0.00 otr 440

You have not given any information on HOW you would normally go about adding these 'creative lanes' or what that means. Are you trying to find sub routes within the major route where your drive can stop along the way? Is the route from Jacksonville to Charleston ALWAYS going to have the same route stops?

In other words (I'm not familiar with the geography of the south for the purposes of this example so bear with me)

Your main route is
Jacksonville to Charleston, but in between those two cities you have the following cities:

Columbus SC
Charlotte NC
Aiken SC
Lumberton NC
Orlando FL
Tampa FL
Gainesville FL
Valdosta GA

And you want to add a 'leg' to the major route for a series of smaller legs of the trip?

[Kreiz]

Yes a lane is one starting city to one ending city.
I am trying to make 1 shipment (Jacksonville FL to Charleston SC) multiply and look like many more shipments, we upload .csv's to trucking sites that display our shipments and we occassionally want to make 1 shipment seem as though it is coming from other cities or going to other cities than what we actually have.

I have a list of "creative or fake" shipments that I copy and paste the city/state combinations to.

If we have a Jacksonville shipment we make it look as though we have one from Tampa, and Orlando, too attact the drivers from outside of Jacksonville. as well the destination, we want to make it look as though we are offering Charleston, as well as Charlotte, as well as Lumberton.

I take the downloaded report of our active shipments, insert 2-5 rows between each row, then add the "creative" cities and states via copy paste from another sheet that we've typed the different combinations

[rpeare]

If you have a pre-defined list of subsidiary routes for each major route that does not change you can do this but it's going to require some code after you select your lane.

Let's say you have a lane table

tblLane
LaneID StartCity    StartSt EndCity    EndSt
1      Jacksonville FL      Charleston SC

then you have a 'sub' lane table

tblSubLane
SubID LaneID SubStart SubSSt SubEnd SubESt
1     1      City1    ST1    City2   ST2
2     1      City2    ST2    City3   ST2
3     1      City2    ST2    City4   ST3
and so on...

Then whenever you add a major lane to a trip you can copy all the subsidiary lanes to your 'legs' of the trip or if you're just trying to show the routes available you can use a union query to show the item from the lane table and the same fields from the sublane table at the same time.