using a qry to run a report with Dmax Function

[mrjoshuaw]

Ok, I have a report that references a qry:

qryJobTicket

This qry pulls from multiple tables to create a job ticket for the technician/installer. I will not list all the tables because I have narrowed down my problem to two specific ones. These two tables:

tblBusinessCustomer
tblBillingInfo

Have a one to many relationship, due to a single customer could have multiple billing notes, this is how the tables are structured:

tblBusinessCustomer:

• CustomerID (Primary Key)
• CustomerName
• Account Status
• InstallationDate

tblBillingInfo:

• BillingID (Primary Key)
• CustomerID
• BillingNotesDate
• BillingNotes

I have a one to many relationship using the CustomerID field from tblBusinessCustomer to tblBillingInfo. When I run the report I have a field for billing notes. So what happens is that when I run the report (using the qryJobTicket as a filter) the report will have as many iterations as there are Notes. So for example:


CustomerID: 1
BillingNotesDate: 1/1/12
BillingNotes: (some form of gibberish)
BillingNotesDate: 1/2/12
billingNotes: (different gibberish)


so the report will run twice, one for each "line" of notes. I have tried using a form of the Dmax function as a filter in the qry so that I would only grab the most recent date from the BillingNotesDate field, thus only running a report and grabbing the most recent info.




The problem is that I have inherited trying to clean up this database, and I added the BillingNotesDate field, since before they were running a constant "log" in the notes field:


1/1/12 Customer changed Name to ABC company, 1/2/12 Customer was notifited of late payment, 1/3/12....and on and on.


SO there are NULL values in the BillingNotesDate field for all of the OLD data. And instead of trying to clean that up right away, I was trying to use the Dmax function to give me the most recent date, but the ones that had no date at all (NULL) it would not pull that record.



That is probably a long way to say I am trying to use the DMax function on a Date Field with Null values, but I figured the more info the better. I apologize for the long post, and thanks for any help that is provided!

[June7]

All you want is the last note record? Try this. Instead of DMax trying to filter the joined tables, create and save a query of tblBillingInfo that returns the last record for each customer then join that query to tblBusinessCustomer. Join type 'show all records from tblBusinessCustomer ...'

[mrjoshuaw]

Ok, I apologize for the late reply, been out of the office for an extended period of time and just now getting back to this problem. I tried the qry path you laid out above but I was still having issues, after doing some dummy querys to see where this may be broke I think I came across the issue and I believe it is because of the design of the database, here is what I found:

tblCustomerID
-CustomerID (Primary)
-PropID
-CustomerName

tblPropID
-PropID (Primary)
-PropAddress

tblOutsidePlant
-OutsidePlantID (Primary)
-PropID
-DP
-FI

tblBillingID
-BillingID (Primary)
-CustomerID
-BillingNotesDate
-BillingNotes

the relationships are:

tblCustomerID.PropID (Many)--->(1) tblPropID.PropID
tblCustomerID.CustID (1) ---> (Many) tblBillingID.CustomerID
tblPropID.PropID (1) ---> (Many) tblOutsidePlantID.PropID

I think the problem that I have is that I can have a customer with more than one BillingID (thus the original problem listed above) AND a customer is related to ONE Property but that property is related to multiple Outside Plant ID's, but the customer should only be related to a single Outside Plant. I have talked to jzwp11 about a join tbl in a previous thread and I was wondering if that is what I would have to do here?

I have tried setting up joined tables but it is not working out like I thought in my head, so I apologize for any confusion above. If anyone needs better clarification let me know, and thanks for all the help!

[June7]

Customer is related to Outside Plant and not Property. Need to change the Customer table. PropID should not be there, it is info about the Plant.

Can each Outside Plant have more than one Customer? If yes, need field in Customer for OutsidePlantID. If no and every Customer must have a Plant, then relationship is one-to-one. Could just combine the Customer and OutsidePlant tables.

[mrjoshuaw]

Customer is related to Property and Outside Plant here is the logical breakdown of how they want their stuff:

Property Address (one Address for a long building) 1000 Swift for example, can have multiple customers (each with a different service address: 1001 Swift, 1003 Swift, etc.) thus the one to many relationship. Also each Property can have multiple Outside Plant info (there is more then one switch port servicing that Property Address), but the customer can only be related to one instance of the Outside Plant info.

Here is what we do, we provide internet service for a small city, I have a cable that feeds a property, that property could have multiple different customers. That cable will have 12 strands lets say, each labeled with a specific id, thus the multiple outside plant to the property, I can have multiple different customers to the property address, thus many to one from customer to propertyid, BUT a customer would only take up one of the strands from the outside plant.

Unless your are talking about getting to the Property Info for the customer through the Outside Plant table? Since the Outside Plant table to the customer table is the one to one relationship?

[June7]

Yes to the last statement.

But you added another element to the picture. You need to associate strand ID with a customer, not just the plant? That pushes the relationship down another level.

[mrjoshuaw]

Sorry about that, no the Strand is part of the plant, in the plant all I have is a DP Number and the Fiber ID, so it would be OutsideplantID 1 would have DP 100 FI 1. I would not have two plant ID's with the exact same DP and FI. Then I would have a customer associated to a plant ID. I said Strand ID when it should have been Fiber ID (FI) sorry for the confusion!

The reason I have it broken up into two tables instead of just having the customers outside plant info in the Customer table is that I could see in the future where we COULD have multiple customers with the same PlantID, that all depends on the design of the network. I did not want to get into the situation where I would have to break it up later, if that makes sense.

Thanks for the help! I will mark this as solved, and any further questions I will reopen another post.