ALLRIGTH! Thanks John, It's working so far, queries are running right. So now to structure the report. I'll get to that this evening after work.
Edgar
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Edgar-
>
> Hmmm. I see your confusion.
>
> You probably want to make note of the exact last service date and time, so
> ServiceDate should remain Date/Time. Interval should also remain an integer.
> What you need to change is qryServices:
>
> SELECT tblServices.IdService, tblServices.IdInventory, tblServices.IdEmp,
> tblServices.ServDate, tblServices.Service, tblServices.Interval,
> IIf(((Month([tblServices].[ServDate])+[tblServices].[Interval]) Mod
> 12)=0,12,(Month([tblServices].[ServDate])+[tblServices].[Interval]) Mod 12) AS
> ProxServ
> FROM tblServices;
>
> Be sure to also remove the Format from the ProxServ field - it should display as
> a number, not a month.
>
> qryActiveCustomers now looks like:
>
> PARAMETERS [Enter Service Month:] Short;
> SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*,
> MonthName([qryServices].[ProxServ]) & ", " &
> IIf(Month(Date())<[qryServices].[ProxServ],Year(Date())+1,Year(Date())) AS
> NextServ
> FROM tblCustomers INNER JOIN ((tblAddresses INNER JOIN tblContacts ON
> tblAddresses.IdAddress = tblContacts.IdAddress) INNER JOIN (tblInventory INNER
> JOIN qryServices ON tblInventory.IdInventory = qryServices.IdInventory) ON
> tblAddresses.IdAddress = tblInventory.IdAddress) ON tblCustomers.IdCustomer =
> tblAddresses.IdCustomer
> WHERE (((tblCustomers.IdStatus)=1) AND ((qryServices.ProxServ)=[Enter Service
> Month:]));
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> rivera_vazquez_edgar
> Sent: Friday, July 08, 2011 1:00 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Help with reports.
>
> John, I'm not quite sure on what to do.
>
> You saying to go ahead and make a table for the months with 3 fields, IdMonth,
> MthNum, and MthName. Then include the IdMonth field in tblServices. If so, I
> wont need ServiceDate field in tblService but you include that field in the code
> for ProxServ. Also, the Interval field will have two posible options for data
> input "6" or "12" because the services are either every 6 or 12 months. I
> mention it because I don't see the 6 in the formula for ProxServ.
>
> Edgar
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Edgar-
> >
> > Instead of putting "January" in the table, put the month number - 1. 2 for
> > February, 3 for March, and so on. If you want to display the month name, use
> a
> > combo box, but store the number. Now ProxServ looks like:
> >
> > IIf((tblService.ServiceDate + tblService.Interval) Mod 12 = 0, 12,
> > (tblService.ServiceDate + tblService.Interval) Mod 12)
> >
> > In your query, use one parameter to prompt for the month number:
> >
> > PARAMETERS [Enter Service Month:] Integer;
> > SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> > FROM tblCustomers INNER JOIN ((tblAddresses INNER JOIN tblContacts ON
> > tblAddresses.IdAddress = tblContacts.IdAddress) INNER JOIN (tblInventory INNER
> > JOIN qryServices ON tblInventory.IdInventory = qryServices.IdInventory) ON
> > tblAddresses.IdAddress = tblInventory.IdAddress) ON tblCustomers.IdCustomer =
> > tblAddresses.IdCustomer
> > WHERE (tblCustomers.IdStatus = 1) AND (qryServices.ProxServ =
> > [Enter Service Month:]);
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > rivera_vazquez_edgar
> > Sent: Thursday, July 07, 2011 1:54 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Help with reports.
> >
> > John, that IdCustomer in tblContacts is not suposed to be there, I erased it.
> > tblContacts is suposed to be linked to tblAddresses not tblCustomers because
> > every contact belongs to an address. I fixed this and now I get data out of
> the
> > query.
> >
> > I'm seeing something that makes me think of changing the way the db records
> the
> > service dates. The way it's designed so far, if I want to see the customers I
> > need to visit for service in july 2011 than I use the 1/jul/2011 to
> 31/jul/2011
> > dates. That will give me the right info, but, what if last year (2010) a db
> > user forgot to change the status of an out-of business customer from active to
> > out-of-business? That customer won't show up as a result in the query because
> > the next service date was for july 2010 and it was not performed, BUT I would
> > not see that the customer status was not changed, for as, if I recive that
> > customer's data when I run the query for july 2011 and see that no service was
> > done last year, I would know that the status was not changed.
> >
> > I don't know if you understand the example but, the thing is, the db we are
> > using right now, althoug very poor in design (reason to make a new one),
> manages
> > the service date by month, reguardless of the year, ofcourse its not a
> DateTime
> > field.
> >
> > What comes to mind is (spit-balling):
> >
> > Make a tblMonth with all 12 months to replace the ServDate field in
> tblServices
> > using a combo box to edit the data...
> > Leave the interval field in tblServices but limit the input value to 12 or 6
> > (months) and for the ProxServ field (next service date), make a series of "if"
> > conditions
> >
> > where (if(tblService.ServDate = "January" and tblService.Interval =
> > "12","January","June")) OR (if(tblService.ServDate = "February" and
> > tblService.Interval = "12","Febuary","July"))
> >
> > and so on for every month. The thing is, would that expression (being so long)
> > work? or would I need to make a query for every month?
> >
> > Edgar
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > >
> > > Edgar-
> > >
> > > You had an error in qryServices. It should be:
> > >
> > > PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> > > SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> > > FROM tblCustomers INNER JOIN ((tblAddresses INNER JOIN tblContacts ON
> > > tblAddresses.IdAddress = tblContacts.IdAddress) INNER JOIN (tblInventory
> INNER
> > > JOIN qryServices ON tblInventory.IdInventory = qryServices.IdInventory) ON
> > > tblAddresses.IdAddress = tblInventory.IdAddress) ON tblCustomers.IdCustomer
> =
> > > tblAddresses.IdCustomer
> > > WHERE (((tblCustomers.IdStatus)=1) AND ((qryServices.ProxServ) Between
> [Enter
> > > From Date:] And [Enter To Date:]));
> > >
> > > When I run qryServices, I find that services are to be scheduled in July
> 2011
> > > for inventory 4, 5, and 6. The addresses for these three items are 3 and 4.
> > > BUT there is no contact linked for those two addresses! Addresses 3 and 4
> are
> > > for customers 4 and 5, respectively. The contacts listed for customers 4
> and
> > 5
> > > are contacts 5 and 6 - but those two contact records do not have a related
> > > IdAddress. The query will return zero rows as long as this is the case.
> > >
> > > Do you mean to link to contacts via IdCustomer? If so, why is there an
> > > IdAddress in tblContacts at all?
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > rivera_vazquez_edgar
> > > Sent: Wednesday, July 06, 2011 9:32 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Help with reports.
> > >
> > > Ok John, I placed a copy of the db on the need asistance folder. the file is
> > > called Fire Service Pro.
> > >
> > > Maybe this way you can actually test the db. Feel free to make changes.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Edgar-
> > > >
> > > > You said the field from qryServices was named NxtServ, so that's why I'm
> > > testing
> > > > for that field. Did you really name it ProxServ?
> > > >
> > > > I guessed at the name of the linking Status field in tblCustomers and the
> > > actual
> > > > underlying value that means "Active". If the field name or the value is
> > > > different, you'll have to change that.
> > > >
> > > > I'm prompting for a "from" - "to" date because I assume "NxtServ" or
> > > "ProxServ"
> > > > is a Date/Time value. The Format of the field has no impact. Even if you
> > > > format the field as "mmm," the underlying value that the query tests is
> > still
> > > a
> > > > full date/time value. If you want the user to be able to enter a month
> > > > abbreviation, then you would need to do:
> > > >
> > > > WHERE Format(qryServices.ProxServ, "mmm") = [Enter month (xxx)
> abbreviation
> > > for
> > > > the next service:]
> > > >
> > > > But that will run much more slowly than entering a specific "from" and
> "to"
> > > > date.
> > > >
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > rivera_vazquez_edgar
> > > > Sent: Wednesday, July 06, 2011 9:01 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Help with reports.
> > > >
> > > > Ok John, I made the changes according to the actual field names. Here is
> the
> > > > update:
> > > >
> > > > PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> > > > SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> > > > FROM (((tblCustomers INNER JOIN tblAddresses ON
> > > > tblCustomers.CustomerID=tblAddresses.IdCustomer) INNER JOIN tblContacts ON
> > > > tblAddresses.IdAddress=tblContacts.IdAddress) INNER JOIN tblInventory ON
> > > > tblAddresses.IdAddress=tblInventory.IdAddress) INNER JOIN qryServices ON
> > > > tblInventory.IdInventory=qryServices.IdInventory
> > > > WHERE tblCustomers.IdStatus=1 And qryServices.ProxServ Between [Enter From
> > > > Date:] And [Enter To Date:];
> > > >
> > > > It's on the right track as to what I need, but can you make these changes?
> > > >
> > > > In the PARAMETERS it should only ask for the date in the field ProxServ
> from
> > > the
> > > > qryService, wich is in the following format (mmm)
> > > >
> > > > Also, instead of asking for the customer id, it should ask for the city
> from
> > > > tblAddress, so i'll get every customer in a given city for a specific
> month.
> > > >
> > > > If that's how it should work the way you wrote the code, than I might be
> > doing
> > > > something wrong.
> > > >
> > > > Edgar
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > wrote:
> > > > >
> > > > > Edgar-
> > > > >
> > > > > Your query might look like:
> > > > >
> > > > > PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> > > > > SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> > > > > FROM (((tblCustomers INNER JOIN tblAddresses
> > > > > ON tblCustomers.CustomerID = tblAddresses.CustomerID)
> > > > > INNER JOIN tblContacts
> > > > > ON tblAddresses.AddressID = tblContacts.AddressID)
> > > > > INNER JOIN tblInventory
> > > > > ON tblAddresses.AddressID = tblInventory.AddressID)
> > > > > INNER JOIN qryServices
> > > > > ON tblInventory.InventoryID = qryServices.InventoryID
> > > > > WHERE tblCustomers.StatusID = 1
> > > > > AND qryServices.NxtServ BETWEEN [Enter From Date:] And [Enter To Date:];
> > > > >
> > > > > I just guessed at the names of the linking fields.
> > > > >
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > rivera_vazquez_edgar
> > > > > Sent: Wednesday, July 06, 2011 2:20 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Help with reports.
> > > > >
> > > > > Ah ok...
> > > > >
> > > > > tblCustomers many to one tblStatus
> > > > > tblCustomers one to many tblAddresses
> > > > > tblAddresses one to many tblContacts
> > > > > tblAddresses one to many tblInventory
> > > > > tblInventory one to many tblServices
> > > > >
> > > > > Now, I made a query (qryServices) to calculate the next service date, so
> > the
> > > > > query has the field "NxtServ" wich is not in tblServices. I use
> qryService
> > > in
> > > > my
> > > > > form to input the data and see the next service date. I guess I need to
> > do
> > > > the
> > > > > same in the report to filter for this date.
> > > > >
> > > > > The report needs to look something like this:
> > > > >
> > > > > 1. Customer's main info from tblCustomer but only for those with
> "Active"
> > > > Status
> > > > > and a specific month in qryServices
> > > > > 2. Customer address from tblAddresses
> > > > > 3. Contact person for that specific address
> > > > > 4. Inventory for that specific address
> > > > > 5. Last service done for each inventory item from qryServices
> > > > > 6. If the customer has another address with service due the same month,
> > then
> > > > > display #2-5 again for each address.
> > > > >
> > > > > Would I need to make a query for every month?
> > > > > I'm preaty sure I need to make a query for every Status if I want a
> report
> > > of
> > > > > the inactive and out-of-business customers also.
> > > > >
> > > > > Let me know if you need more info.
> > > > > Edgar
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > wrote:
> > > > > >
> > > > > > Edgar-
> > > > > >
> > > > > > You say tblAddresses is related to tblCustomer, but you don't say how.
> > I
> > > > > assume
> > > > > > it's many addresses for one customer. How is inventory and service
> > > related
> > > > to
> > > > > > these two tables?
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas.com/
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > > rivera_vazquez_edgar
> > > > > > Sent: Wednesday, July 06, 2011 4:09 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Help with reports.
> > > > > >
> > > > > > I'm still having problems making my report.
> > > > > >
> > > > > > Here is my structure:
> > > > > >
> > > > > > tblCustomer related to tblAddresses. Each address has a contact
> person
> > > and
> > > > an
> > > > > > inventory (tblInventory) and each inventory has a service
> (qryService).
> > > > > >
> > > > > > I need all this info on the report but I need to filter the info by
> two
> > > > fields
> > > > > > in diferent tables/query. There is a table for Status related to
> > > > tblCustomer
> > > > > > and a field for next service date in qryService.
> > > > > >
> > > > > > I need, for example, every active customer with their address and
> > contact
> > > > > person
> > > > > > and the inventory for that address, but only show customers with a
> next
> > > > > service
> > > > > > date of July wich is in a table called tblService related to the
> > inventory
> > > > > > table.
> > > > > >
> > > > > > I'm realy stuck, HELP!
> > > > > >
> > > > > > Edgar
> > > > > >
> > > > > >
> > > > > >
> > > > > > ------------------------------------
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar