ACCESS FEED: RE: [MS_AccessPros] Re: Back with reports problems

Jumat, 29 Juli 2011

RE: [MS_AccessPros] Re: Back with reports problems

I assumed you wanted to get the most recent service date into the main report's record source so that you could set criteria against it. Is this a bad assumption on my part or does your record source already include the most recent service date?

BTW: IMO, parameter prompts have no business in applications. All user interaction should be through forms.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: xtintores@yahoo.com
Date: Fri, 29 Jul 2011 21:57:29 +0000
Subject: [MS_AccessPros] Re: Back with reports problems

I already have that query build. Now the Max on recieved service is the part I dont get. Here is the query:

SELECT tblCustomers.IdCustomer, tblStatus.Status, tblCustomers.AccountNum, tblCustomers.CompanyName, tblCustomers.Notes1, tblAddresses.City, qryServices.ProxServ
FROM tblStatus INNER JOIN (tblCustomers INNER JOIN (tblAddresses INNER JOIN (tblInventory INNER JOIN qryServices ON tblInventory.IdInventory = qryServices.IdInventory) ON tblAddresses.IdAddress = tblInventory.IdAddress) ON tblCustomers.IdCustomer = tblAddresses.IdCustomer) ON tblStatus.IdStatus = tblCustomers.IdStatus
WHERE (((tblStatus.Status)=[ENTRAR STATUS]) AND ((tblAddresses.City)=[ENTRAR CIUDAD]) AND ((qryServices.ProxServ)=[ENTRAR MES DE SERVICIO]));

What should I change?
By-the-way the user input in brakets is in spanish. ENTRAR STATUS is "Enter Status", ENTRAR CUIDAD is "Enter city" and ENTRAR MES DE SERVICIO IS "Enter service month".

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> You can create a query that groups by customer with the Max of the received service. Then join this to you customer table so you now have the most recent service with all the customer fields.
>
> If you can't figure this out, you can always type some records into a reply (significant fields) and tell us how and why you would expect these records to render in a report.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: xtintores@...
> Date: Fri, 29 Jul 2011 12:22:30 +0000
> Subject: [MS_AccessPros] Re: Back with reports problems
>
>
>
>
>
>
> Ok, let me explain...
>
> One of the querys is called qryService witch has these fields: IdService (PK), IdInventory (FK), IdEmp (FK), ServDate, Service, Interval, ProxServ.
>
> Now, ProxServ has the value that I need to populate (sort) the report, but, an inventory item might have recived service in febuary of 2008, febuary of 2009, and the last service done to it was in MARCH of 2010. I need the report to show me this customer only in march witch was the last month that the customer recived service. If I ask the report to show me the customers for febuary, I only want the customers that recieved service in febuary of last year, not 2 years ago. Unless no service was done last year in witch case, febuary of 2009 would be the last service done to that customer and it should show inthe report.
>
> Edgar.
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >
> >
> > There is no "last record on the field" that I understand. Do you mean the last character of a field? If so, you can sort/group on expressions like:
> > =Right([Your Field Name],1)
> >
> > Duane Hookom
> > MS Access MVP
> >
> >
> >
> >
> > To: MS_Access_Professionals@yahoogroups.com
> > From: xtintores@
> > Date: Wed, 27 Jul 2011 22:23:51 +0000
> > Subject: [MS_AccessPros] Re: Back with reports problems
> >
> >
> >
> >
> >
> >
> > Thank you Duane, I found the Grouping & Sorting and it worked. Now, one more thing, how do I specify the report to group and sort only by the last record on the field?
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> > >
> > >
> > > Use the Sorting and Grouping dialog or the Group & Sort functionality. This depends on your version of Access.
> > >
> > > You can also GROUP BY in your reports record source but this will possibly remove some granularity of your data.
> > >
> > > Duane Hookom
> > > MS Access MVP
> > >
> > >
> > >
> > >
> > > From: xtintores@
> > >
> > >
> > >
> > >
> > >
> > >
> > > Hello group...
> > > I'm back with a small problem with my report. It's almost complete but I'm having problems with duplicate values in the report. How do I group the report results by date?
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>