Using DMax for Finding Highest Date of Order

[jlundbe2]

Hello everyone!

I am currently building a system to track advertising in a newspaper. I have the following tables (that relate to this question): tblContracts, tblOrder. There are related by ContractID. The order has an advertising date for each line item... as such, one contract can have an ad on 8/17, 8/18, and 8/19 (each with a different LineOrderID). I would like to create a datasheet form that pulls all closed contracts that don't have an initial billing date (i.e. those that contracts are complete but have yet to be billed). Is this possible using DMax or a combination of various queries?

The ultimate goal is to pull a list of all contracts that have a final issue date less than the current date so to bill each of the completed contracts.

Let me know if you have any other questions, thanks for all of the support!

Jason.

 

[DCrake]

Simple Software Solutions

In lay terms you want to create a query that pulls of all orders where the advertising date is less than today which don't have a billing date, sort by billing date.

What you have not mentioned is the billing terms for the contract bills may not need to be raised until their agreed payment terms have been reached.

CodeMaster:

 

[jlundbe2]

Yes, in laymans terms that is correct. I am trying to pull all contracts whose greatest advertising date is less than today and do not have a billing date.

The payment terms are the same for all contract - once all advertisements are ran and confirmed, there is a yes/no check box on each line order for confirming the ad running, the contract can be billed. I am just not sure how to design this. I have a relational diagram of the tables that I can post if that will help.

Thanks!

 

[jlundbe2]

Anyone have any ideas? I am stumped.

 

[stopher]

Does the attached example help? There are two queries. The first query (qryLatestDate) lists the latest OrderLineID/Date for each contract. The second query joins the first query with tblContracts to enable you see a list of contracts with the latest order date for each contract. Obviously you can add more fields and filters at each stage. In the first query I filtered out orders with billing dates.

hth
Chris