[MS_AccessPros] Re: Help with reports.

 

Brad

As I explained in an email to you, members get all posts when using the email option. John did not send a response directly to you. If you look at the "To" address you will see it is our Yahoo group address.

Bill Mosca,
Founder

--- In MS_Access_Professionals@yahoogroups.com, Brad Griffis <bradgriffis@...> wrote:
>
> Hello John, I think you replied to the wrong person. I did not create a post with the subject "Help with reports."
>
> Brad Griffis
>
> --- On Thu, 7/7/11, John Viescas <john@...> wrote:
>
> From: John Viescas <john@...>
> Subject: RE: [MS_AccessPros] Re: Help with reports.
> To: MS_Access_Professionals@yahoogroups.com
> Date: Thursday, July 7, 2011, 12:26 AM
>
>
>
>
>
>
>
>  
>
>
>
>
>
>
>
>
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (17)

Recent Activity:

• New Members 5
• New Links 1
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___