ACCESS FEED: Re: [MS_AccessPros] Re: Help with reports. -- OT: members receive all posts

Hi Brad,

> "Please only send replies to my post"

Welcome! All members receive all posts to the group. You can use your email application to set up filters to direct posts from MS_Access_Professionals containing particular words in the subject to a specific folder

Warm Regards,
Crystal

*
(: have an awesome day :)
*

--- On Thu, 7/7/11, Brad Griffis wrote:

> I am a new member and received this
> email by mistake. Please only send replies to my post.
> Thanks.
>
>
>
> Brad Griffis
>
> --- On Thu, 7/7/11, rivera_vazquez_edgar
> wrote:
>
>
>
> 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
>
> > > > >
>
> > > > >
>