Thread: Access Help
01-22-14, 15:02 #1Registered User
- Join Date
- Jan 2014
Unanswered: Access Help
I'm new to using access and I have been given what I thought was a minor project. Currently I have data importing into Access from our billing system - this data comes across with either 1 or 2 lines of data. Line one is always the "service" portion of the billing whereas line 2 is a quantity based charge.
Incoming Data looks like this
CUSTOMER_NO Quantity Charge CODE## TICKET
189601 1 $100.00 40C 579701
189601 8.07 $807.00 DTN 579701
Outgoing data request:
CUSTOMER_NO Service Weight Charge Total
189601 $100.00 8.07 $807.00 $907.00
Any advice on how I would go about combining these rows? There are two "weight" based codes - the DTN you see above as well as TNY. I assume the solution would involve some if, then statement else the weight/charge = 0.
Thanks in advance for any help.
01-22-14, 16:15 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
If you are certain about `its only one or two lines per customer and the weighted riw is either dtn or tny then you could do what you want through a JOIN. Im guessing you are probably going to be using the query designer...
So place the table containing your incoming data on the query designer TWICE.
Drag and drop the customer no from one table to the other
Right click the resulting line (the relationship) and define the realtionship as all rows from the first table and all that match from the other.
Drag and drop the columns you want from each table
In the first table define the code criteria asCode:
NOT IN("DTN", "TNY")
Then save and run the query.
Bear in mind it may take a few iterations to get the query rightI'd rather be riding on the Tiger 800 or the Norton