DLookup/DMax?

[Rich Bernat]

We have vending machines which are serviced by emptying the money
inside the machine and placing it into numbered bags. Each bag is
numbered independently of the machines. Each machine has a sales
meter which tells me how much money should be in each bag.

I have two tables:

tblSales, which contains fields [MachineID], [CollectDate], [Bag] and
[Meter]

tblCounts, which contains fields [CountDate], [Bag] and [Amount]

The bags are counted, at the earliest, on the date they were
collected. In other words, tblCounts.[CountDate] >=
tblSales.[CollectDate]

Is there a way I can match up tblCounts.[Amount] to tblSales.[Meter]
using DLookup? My problem lies in the fact that there are multiple
matches in tblCounts.[Bag] for tblSales.[Bag]. These are the
criteria:

tblCounts.[Bag] = tblSales.[Bag]
tblCounts.[CountDate] >= tblSales.[CollectDate]

and finally, where I'm having problems, is that I need the record that
contains the maximum of tblSales.[CollectDate] AND meets the above
criteria. I have been trying various combinations of DLookup and
DMax, to no avail.

Obviously I am a shadetree Access user with no programming skills. I
am hoping that this can be accomplished via a query expression.

Thanks,
Rich

[Phil Stanton]

I think you should perhaps look at it a different way
1 Table Machines containing MachineID, Meter and DateEmptied
1 Table Bags containing BagID (perhaps this could be a permanent number
written on the bag
MachineID (This would change depending on which machine had been
emptied)
DateCounted and Amount
The query joining the 2 tables will show the Meter and the Amount
You would have to devise methods of setting the DateCounted and Amount to
zero
Presumably the DateEmptied wiuld be assigned as the MachineID FK was entered
into the bag table

Phil
"Rich Bernat" <rb*****@autofrost.net> wrote in message
news:9c**************************@posting.google.c om...

We have vending machines which are serviced by emptying the money
inside the machine and placing it into numbered bags. Each bag is
numbered independently of the machines. Each machine has a sales
meter which tells me how much money should be in each bag.

I have two tables:

tblSales, which contains fields [MachineID], [CollectDate], [Bag] and
[Meter]

tblCounts, which contains fields [CountDate], [Bag] and [Amount]

The bags are counted, at the earliest, on the date they were
collected. In other words, tblCounts.[CountDate] >=
tblSales.[CollectDate]

Is there a way I can match up tblCounts.[Amount] to tblSales.[Meter]
using DLookup? My problem lies in the fact that there are multiple
matches in tblCounts.[Bag] for tblSales.[Bag]. These are the
criteria:

tblCounts.[Bag] = tblSales.[Bag]
tblCounts.[CountDate] >= tblSales.[CollectDate]

and finally, where I'm having problems, is that I need the record that
contains the maximum of tblSales.[CollectDate] AND meets the above
criteria. I have been trying various combinations of DLookup and
DMax, to no avail.

Obviously I am a shadetree Access user with no programming skills. I
am hoping that this can be accomplished via a query expression.

Thanks,
Rich

[Rich Bernat]

Thanks for your reply. Sorry for not specifying this, but each bag
does have a permanent number written on it.

Ideally, I would like to have MachineID included in tblCounts, but
that is just not feasible. What bothers me is that I have enough
information to match the bag to the machine based on the criteria but
am just unable to accomplish it yet. I still think it is possible
with an expression using DLookup and/or DMax.
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message news:<3f*********************@mercury.nildram.net> ...

I think you should perhaps look at it a different way
1 Table Machines containing MachineID, Meter and DateEmptied
1 Table Bags containing BagID (perhaps this could be a permanent number
written on the bag
MachineID (This would change depending on which machine had been
emptied)
DateCounted and Amount
The query joining the 2 tables will show the Meter and the Amount
You would have to devise methods of setting the DateCounted and Amount to
zero
Presumably the DateEmptied wiuld be assigned as the MachineID FK was entered
into the bag table

Phil

[Phil Stanton]

Whilest the MacchineID is not directly held in the tblCounts, it is visible
when the MachineID is linked to the Bag.

In the end I guess to want to end up with a table that is

MachineID Meter DateEmptied DateCounted Amount

I supect that for your audit purposes the Bag is irrelevant and only used as
a method of updating the above table

Phil
"Rich Bernat" <rb*****@autofrost.net> wrote in message
news:9c**************************@posting.google.c om...

Thanks for your reply. Sorry for not specifying this, but each bag
does have a permanent number written on it.

Ideally, I would like to have MachineID included in tblCounts, but
that is just not feasible. What bothers me is that I have enough
information to match the bag to the machine based on the criteria but
am just unable to accomplish it yet. I still think it is possible
with an expression using DLookup and/or DMax.
"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message

news:<3f*********************@mercury.nildram.net> ...

I think you should perhaps look at it a different way
1 Table Machines containing MachineID, Meter and DateEmptied
1 Table Bags containing BagID (perhaps this could be a permanent number
written on the bag
MachineID (This would change depending on which machine had been emptied)
DateCounted and Amount
The query joining the 2 tables will show the Meter and the Amount
You would have to devise methods of setting the DateCounted and Amount to zero
Presumably the DateEmptied wiuld be assigned as the MachineID FK was entered into the bag table

Phil