Looking for a solution to a relationship between 2 tables

[MrByte]

I may have just figured it out but I thought I would throw it out there and see if anyone has a different solution. I work a pizza delivery company and I get a printed out receipt for each delivery I make. On there is a delivery date, order number, order time, order out, address, etc. I created a table just for those tickets and everything was working fine until I wanted to start tracking my mileage.

So I thought lets create another table with just the delivery date, order number, trip number, trip amount, trip start (starting mileage), trip stop (ending mileage) and a calculated field named distance. The way I see it there is no unique filed between the two tables. I cannot use order number because there may be the same order number today that I would deliver tomorrow. Then I thought maybe I can concatenate a unique ID that would work between the two tables. To which I learned you cannot do that in Access or at least I got an error message saying I couldn't create a relationship between the two tables using a calculated field.

So I am not really wanting to do this but I would just add the fields from my tblMileage to my tblTicketData. What are your thoughts? Is there a way I can have the two separate tables or should I just combine them?

Thanks for the ideas.

 

[shg]

I'm not an Access user, but it seems like the key should be the date and order number: yyyy-mm-dd-ooo

 

[MrByte]

I did try creating a unique ID using the calculated method of Day([DelDate])&IIF([OrderNum]<10,'0'&[OrderNum],[OrderNum]) which would result in an example like 1401. If this was the first delivery I did for today.

 

[shg]

Reckon that would work for single month -- but I know nothing about Access.

 

[welshgasman]

I'd just have one table and have the start and end mileage in it.?
None of the orders are linked as far as I can see.

You can then total your miles, per week,month and year with a query.?

HTH

 

[xenou]

I don't see anything wrong with mileage in the first table either - if you want a specific mileage for each ticket it can be in there as a specific attribute of that ticket. Otherwise, in general an ID field, even an autonumber ID field, would also be a way to join fields on separate tables.