Mixing to Tables Together

  • Thread starter Thread starter Phantom_guitarist
  • Start date Start date
P

Phantom_guitarist

How can I take two table as follows

Table 1 Mileage
Date
Destination
Miles

Table 2 Petrol Reciepts
Date
Petrol

And combine them into 1.

Table 3
Date
Miles
Petrol

The problem is that not all the dates in Table 2 are in Table 1. So a new
entry needs to be made with the date and petrol when this happens.
If possibly I would like to find the next date for this and add the petrol
cost to the end of that.

Eg,.

Table 1 21/11/03 Blackpool 103 miles
Table 2 20/11/03 £25.36

When Combined gives

Table 3 21/11/03 Blackpool 103 miles £25.36


Has anyone any auggestions?
 
I'm not seeing how you expect to relate the two tables ... is date the only
shared field? How will you resolve more than one destination on the same
date?

I'm not understanding the "rule" you wish to use to add petrol to trip info.
And what will you do if there's a destination/trip that is so long that you
need to fill the tank more than once -- you only mention one field for
"PetrolCost".

I suspect your data would benefit from spending a bit more time normalizing.

Good luck

Jeff Boyce
<Access MVP>
 
Yes, Basically the date is the only relation between the two tables. If
there is more than one destination (which chances are there isn't) then the
destination field would have all destinations in it.
Eg.

Destination: Manchester
Destination: Manchester/Liverpool

If you needed to fill up more than once (which isn't a real issue) I would
create some way of having a running total for that day (ie. group dates and
sum the petrol cost). Which should be easy to set up.

With respect to the rule. If I fill up on a day I do business miles it will
appear on that line.
If I decided instead of filling up monday morning I'll fill up Sunday night
I would like the reciept to appear on the monday line.
 
Create 2 queries.

Query 1, will be a union query. Save it as qry_Union

SELECT tvlDate as fldDate, Miles, 0 as Petrol
FROM Mileage
UNION ALL
SELECT PetrolDate as fldDate, 0 as Miles, Petrol
FROM [Petrol Receipts]

Query #2 will use qry_Union as its data source

SELECT fldDate
, Sum(Miles) as DailyMiles
, Sum(Petrol) as DailyPetrol
FROM qry_Union
GROUP BY fldDate

Note that I have changed the name of your date fields. DATE is an
Access reserved word, and should not be used as a field name if you
can help it. Make the fieldname more descriptive tvlDate for travel
date, petrolDate for dates of petrol purchases, I use fldDate as the
name in the union query to make it obvious that this date corresponds
to more than just a travel or petrol date.


--
HTH

Dale Fye


"Phantom_guitarist"
How can I take two table as follows

Table 1 Mileage
Date
Destination
Miles

Table 2 Petrol Reciepts
Date
Petrol

And combine them into 1.

Table 3
Date
Miles
Petrol

The problem is that not all the dates in Table 2 are in Table 1. So a
new
entry needs to be made with the date and petrol when this happens.
If possibly I would like to find the next date for this and add the
petrol
cost to the end of that.

Eg,.

Table 1 21/11/03 Blackpool 103 miles
Table 2 20/11/03 £25.36

When Combined gives

Table 3 21/11/03 Blackpool 103 miles £25.36


Has anyone any auggestions?
 
JOPO (just one person's opinion)

By your description, you are using Access more like a spreadsheet, and not
taking advantage of the relational design. If you only need to
combine/track the type of data from your description, why not use a
spreadsheet?

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top