Help with relationships

[clive2011]

Hi.

I'm developing a database to show vehicle usage across the company that shows mileage per vehicle on a daily basis along with the dates that a given vehicle fillled up with gas - how much gas etc.

I have a master file with vehicle info.

I have a table that records when each vehicle fills up with gas.

I have a table that records GPS data from each vehicle and calculates mileage driven on a daily basis.

Right now the relationships are built up as a one to one. The VIN is unique so its a primary key in the master file. In the Gallons and Mileage tables, the VINS will appear multiple times, every day for the mileage table, and in the gallons table by the days that they fill up.



Right now with a one to one relationship, because the gallons table will only have records for the days that vehicles filled up, it will not show records from the mileage table where vehicles could have been driving.

How can I make it return rows that show all mileage records and gallons records too - even if no gas was purchased, in which case null or a substitued value could be displayed.

The only unique value is the VIN. However the VIN does not appear in the mileage table - but the Tech ID does so created a table with VINS and TECHS that creates a bridge.

I'm at a loss. Any ideas? I would attach the database but it contains sensitive info but I can further describe the situation if there are questions.

Thanks

Clive

[alansidman]

It would be helpful if you posted a sanitized copy of your db. Make sure to run a compact and repair and remove(make dummy info) for any confidential information.

[clive2011]

I just tried uploading. even after pruning down what was not appropriate to send and only leaving table structures, it would not upload.

The overall size is 796MB even after pruning down. The max upload size here is 500KB which is next to nothing.

The next best thing although not ideal was to take a screen dump so you can see the relationships and the tables I am dealing with

Thanks

[clive2011]

Hi again,

I missed a key point in your reply - to compact database (duh on my part)

It should now upload.

here it is.

Your help is appreciated.

Thanks

[alansidman]

You have no sample data included. Please load some sample data and I will relook at the db.

Alan

[clive2011]

Thanks Alan,

A colleague came to the rescue instead.

Thanks for your offer of help though.


Regards

Clive