How to display 2 fields from seperate records

[naiku]

Ok how to explain this (and hoping its the most appropriate forum) I have a main form which displays customer information, on that main form is a subform which displays service visits to the customers site. From this subform each month reports are generated for engineers to take along on service visits to the customers site.

Now for the problem, how can I display the previous service date on both the subform and the report? What I basically want is the previous service date, and then from this the next service date would be automatically generated (the form/reports already generate this) So really it's more for convenience that I want the previous service date to be also displayed. (I have not had a chance to try anything yet, so don't know if this is easy, or if I already know how to do it)

The customer information is saved in 1 table, with a one to many relationship onto the table which contains the service information. These tables are what the form/subform are based on. Hope this all makes sense, hope someone can help.

Thanks

 

[naiku]

I take it this either can't be done.... Will have to get my thinking cap on then.

 

[RickSpr]

I can see two reasons you didn't get a quick answer:
1. It's been the weekend. There is somewhat less attention given to Tek-Tips on the weekend.
2. You admitted you hadn't even thought about this enough to know whether you already know the answer. It's only polite that you try to work out your problems for yourself, before you take up other people's time with them. We're here to help you when you get stuck, but not to do your thinking for you.

Now to address your question:
I would think you could use an unbound control on the main form, and use the form Current event to run a query involving the DMax() aggregate function, to obtain the latest service date, which you then store in the unbound control.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pelegs]

Only thing I would add is that instead of using DMax, write your own Select. All of the the Domain Aggregate functions are known to be terribly slow. The SQL to do it yourself is simple:

Select column From table Order By column Desc

and then just return the value of the column in the first row that comes back, which is where the query leaves you by default (so a movefirst is not necessary). Of course, you may want to first check that you indeed did get a row back in case there has been no previous service visits.



Peleg
PelegNOSPAM@PStrauss.net