Can it be done???

[cureless]

Alright so here is the question. I have taken over paying for fuel surcharges where I work, but since I have to go back to school soon, I was asked to try and streamline the process of figuring the price for the fuel surcharge. At this point I was trying to accomplish this in excel but thought maybe Access would work better.

I have 7 columns. And about 68 rows. Technically two tables. One table is a long list of dates and the corresponding Department of Energy fuel price for the week.

The second chart is a long list of fuel price ranges, their percentage, the percentage in decimal, the contract price per CWT and the end total.

GOAL: I want to be able to search for a date, for example 10/18/12, and have the system spit out the end total. (The example is in red) So that would mean the Date I enter would connect to the DOE price, which would then match to one of the price ranges in table to which would give the total. If that makes sense. I have included what is in the the excel file with all the data to hopefully make more sense.

If you need more of an explanation just let me know!

Appplicable	DOE				Surcharge	Price	Fuel
Wk Beginning	PRICE		National Avg.	Surcharge	in decimal	Per cwt	Surcharge
12/26/11	3.791		1.110-1.179	1%	0.01	3	0.03
1/8/12	3.783		1.180-1.249	2%	0.02	3	0.06
1/15/12	3.828		1.250-1.319	3%	0.03	3	0.09
1/22/12	3.854		1.320-1.389	4%	0.04	3	0.12
1/29/12	3.848		1.390-1.459	5%	0.05	3	0.15
2/5/12	3.850		1.460-1.529	6%	0.06	3	0.18
2/12/12	3.856		1.530-1.589	7%	0.07	3	0.21
2/19/12	3.943		1.590-1.649	8%	0.08	3	0.24
2/26/12	3.960		1.650-1.709	9%	0.09	3	0.27
3/4/12	4.051		1.710-1.769	10%	0.1	3	0.3
3/11/12	4.094		1.770-1.829	11%	0.11	3	0.33
3/18/12	4.123		1.830-1.889	12%	0.12	3	0.36
3/25/12	4.142		1.890-1.949	13%	0.13	3	0.39
4/1/12	4.147		1.950-2.009	14%	0.14	3	0.42
4/8/12	4.142		2.010-2.069	15%	0.15	3	0.45
4/15/12	4.148		2.070-2.129	16%	0.16	3	0.48
4/22/12	4.127		2.130-2.189	17%	0.17	3	0.51
4/29/12	4.085		2.190-2.249	18%	0.18	3	0.54
5/6/12	4.073		2.250-2.309	19%	0.19	3	0.57
5/13/12	4.057		2.310-2.369	20%	0.2	3	0.6
5/20/12	4.004		2.370-2.429	21%	0.21	3	0.63
5/27/12	3.956		2.430-2.489	22%	0.22	3	0.66
6/3/12	3.897		2.490-2.549	23%	0.23	3	0.69
6/10/12	3.846		2.550-2.609	24%	0.24	3	0.72
6/17/12	3.781		2.610-2.669	25%	0.25	3	0.75
6/24/12	3.729		2.670-2.729	26%	0.26	3	0.78
7/1/12	3.678		2.730-2.789	27%	0.27	3	0.81
7/8/12	3.648		2.790-2.849	28%	0.28	3	0.84
7/15/12	3.683		2.850-2.909	29%	0.29	3	0.87
7/22/12	3.695		2.910-2.969	30%	0.3	3	0.9
7/29/12	3.783		2.970-3.029	31%	0.31	3	0.93
8/5/12	3.796		3.030-3.089	32%	0.32	3	0.96
8/12/12	3.850		3.090-3.149	33%	0.33	3	0.99
8/19/12	3.965		3.150-3.209	34%	0.34	3	1.02
8/26/12	4.026		3.210-3.269	35%	0.35	3	1.05
9/2/12	4.089		3.270-3.329	36%	0.36	3	1.08
9/9/12	4.127		3.330-3.389	37%	0.37	3	1.11
9/16/12	4.132		3.390-3.449	38%	0.38	3	1.14
9/23/12	4.135		3.450-3.509	39%	0.39	3	1.17
9/30/12	4.086		3.510-3.569	40%	0.4	3	1.2
10/7/12	4.079		3.570-3.629	41%	0.41	3	1.23
10/14/12	4.094		3.630-3.689	42%	0.42	3	1.26
10/21/12	4.150		3.690-3.749	43%	0.43	3	1.29
10/28/12	4.116		3.750-3.809	44%	0.44	3	1.32
11/4/12	4.030		3.810-3.869	45%	0.45	3	1.35
11/11/12	4.010		3.870-3.929	46%	0.46	3	1.38
11/18/12	3.980		3.930-3.989	47%	0.47	3	1.41
11/25/12	3.976		3.990-4.049	48%	0.48	3	1.44
12/2/12			4.050-4.109	49%	0.49	3	1.47
12/9/12			4.110-4.169	50%	0.5	3	1.5
12/16/12			4.170-4.229	51%	0.51	3	1.53
12/23/12			4.230-4.289	52%	0.52	3	1.56
12/30/12			4.290-4.349	53%	0.53	3	1.59
1/6/13			4.350-4.409	54%	0.54	3	1.62
			4.410-4.469	55%	0.55	3	1.65
			4.470-4.529	56%	0.56	3	1.68
			4.530-4.589	57%	0.57	3	1.71
			4.590-4.649	58%	0.58	3	1.74
			4.650-4.709	59%	0.59	3	1.77
			4.710-4.769	60%	0.6	3	1.8
			4.770-4.829	61%	0.61	3	1.83
			4.830-4.889	62%	0.62	3	1.86
			4.890-4.949	63%	0.63	3	1.89
			4.950-5.009	64%	0.64	3	1.92
			5.010-5.069	65%	0.65	3	1.95

[ssanfu]

Welcome to the forum.

And yes it can be easily done.
Two tables, but the "National Avg." would need to be two fields - Lower and Upper (or Low - High).
First search on date, then use the DOE Price to search (lookup) the correct record in the second table.
Several ways to do it.

[June7]

The NationalAvg needs to be two fields: AvgMin and AvgMax.

The WkBegin and DOEPrice fields are in one table - tblDOE

The other fields are in another table - tblSurcharge (either Surcharge or Decimal and FuelSurchage aren't really needed - these can be calculated)

SELECT AvgMin, AvgMax, Surcharge, PriceCWT, [Surcharge]*[PriceCWT] AS FuelSurcharge, DLookUp("DOEPrice","tblDOE","WkBegin=#" & [Enter beginning of week date] & "#") AS DOEPrice
FROM tblSurcharge
WHERE (((DLookUp("DOEPrice","tblDOE","WkBegin=#" & [Enter beginning of week date] & "#")) Between [AvgMin] And [AvgMax]));

EDIT: Hi ssanfu, I was composing while you posted!

[cureless]

Thank you both! The way it is being done now is, or at least when I was handed the charts, I have two pieces of paper. Each has the above charts. And it was just annoying to switch between them both. So I merged them both onto a single sheet, I know, rocket science but apparently no one had figured that one out yet .

Is there any way to be able to do it all at once? for example just the initial date, rather than searching the first, then the second? Because in reality it doesn't save me any more time that just looking at the paper.

Once again thanks for your help!

[June7]

I gave you an example query that is an all-in-one action.

Build tables as described. Copy/paste the query into SQL view window of Query Designer. Open query in Datasheet view, respond to prompt for date.

If that's as long as your lists will get, then Access might be overkill, unless you are incorporating this lookup into a much larger database application.

[cureless]

Thanks!

I don't plan on incorporating it into any larger databases. I have many different charts because I pay more than ten different companies at different rates. Most annoying thing ever. But no other databases. I'm just trying to simplify paying the fuel surcharges and many other functions of the job. And I have no experience with Access, and I figure if I don't most people who would take my place when I leave wouldn't be able to figure it out and maintain the system. So I may just figure it out to help me learn something new but give my replacement something easier.

Thanks for the help!

[ssanfu]

I had a couple of minutes so I threw together a example. It is in Access 2000, but you should still be able to open it. Didn't know what you wanted to see, so I display all data.

I have many different charts because I pay more than ten different companies at different rates

You could expand this to have multiple companies by adding another field to the table "National" (National Avg data table) and another combo box to select the company.


June: I'm starting to learn how to type..... LOL