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 3 4581
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*****@autofr ost.net> wrote in message
news:9c******** *************** ***@posting.goo gle.com... 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
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********@sta ntonfamily.co.u k> 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
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*****@autofr ost.net> wrote in message
news:9c******** *************** ***@posting.goo gle.com... 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********@sta ntonfamily.co.u k> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: KLAU |
last post by:
I have a field that retrieves information from an expression in a
query. I have used a DLookup function to get the calculated field
from the query. However, the relationship is 1-to-many so one site
could have many units. How do I have the DLookUp field value change
to a newly calcuated field when I navigate through the units? please
see example below:
On the Form:
A site can have many units.
|
by: lukeargent |
last post by:
Hi All,
I have come across some rather weird error that I can only assume is
something to do with my ADP file connecting to SQL Server 2000.
I'm using Access XP as my front end.
In simple terms:
I have a database which is in design for a new Development Request
system. The main 2 tables are linked one to many, by in this instance
say DWR_ID.
|
by: Beejer |
last post by:
I'm running Access 2003 on WinXP
Here is my problem:
I'm trying to lookup a date in a table from a query data. The query
include the following: Query = Qry_Invoice and Table = Tbl_Friday
Qry_Invoice contains invoice data like: supplier name, invoice date,
payment date, purchase order.
|
by: AricC |
last post by:
Howdy Errrrbody,
I'm new here! I have a few questions that I can't seem to get answered, maybe you all can help.
I am writing an access program ( first time I'm accustomed to .Net + Sql Server ) I am trying to select the Max ID from a table and store so I can use that ID in a referenced table here is what I have tried:
intReturnGoodsAuthorizationID = DMax("", "")
This should return 10 (the highest ID) but it returns 0
|
by: gps |
last post by:
I'm trying to use Dmax to dip into a my invoice header table and pull the maxium invoice number out of it.
I'm running Access 2007 on Vista Business, but had the same prob with Access 2003 on XPP.
Here's a snapshot of the table data:
InvoiceID
79571
79572
ACCT
ACCT
| |
by: joeyrhyulz |
last post by:
Hi,
I'm trying to make a very simple update statement (in Oracle) in jet
sql that seems much more difficult than it should be.
The root of my problem is that I'm trying to update a field on a table
using dmax, which references another query to update the table.
Although I have all of the correct keys from the physical table joined
to the query in the dmax function, the code/ms access seems to ignore
the joins. As a result, all payees...
|
by: |
last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the application's reports I have a page footer with an unbound field that retrieves and shows the name of the company. This is done by Dlookup. My question is: is Dlookup the best way to do this, performance wise, or is there a more efficient way?
Thanks in...
|
by: rleepac |
last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to assign an Age Correction value to hearing test results - since some degree of hearing loss naturally occurs with aging - OSHA lets us calculate that in before determining if the employee has an actual "significant" hearing loss.
Anyway...I have my...
|
by: DanicaDear |
last post by:
I have a report named "rptHOTSTICKS_EXPIRING" based on a query named "HOSTICKS_SHIPPING_REPORT Query". The query contains these fields:
ORDER_NUM (text)
CUST_NUM (text)
Name, address, contact info, I think not important to this thread
DATE_SHIP (date)
Then I have a field
STICKS_EXPIRE: DateAdd("yyyy",2,)
with criteria
Between And
where and are parameters.
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |