Kat-
Completely and utterly WRONG. Why would you link the ID from employees to
Contract ID? Why do you think Vendor ID is related to Contract ID when there's
a perfectly good Vendor ID in Contract Information? Do you not have a clue?
The SQL for the corrected query looks like:
SELECT [CONTRACT INFORMATION].[CONTRACT START DATE], [CONTRACT
INFORMATION].[CONTRACT END DATE], [VENDOR INFORMATION].[VENDOR E-MAIL],
[CONTRACT INFORMATION].[CONTRACT ID], [EMPLOYEE INPUT INFORMATION].[E-MAIL],
[VENDOR INFORMATION].[CONTACT PERSON], [CONTRACT INFORMATION].[VENDOR ID]
FROM [VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION] INNER JOIN
[CONTRACT INFORMATION] ON [EMPLOYEE INPUT INFORMATION].ID = [CONTRACT
INFORMATION].[EMPLOYEE ID]) ON [VENDOR INFORMATION].[VENDOR ID] = [CONTRACT
INFORMATION].[VENDOR ID]
WHERE ((([CONTRACT INFORMATION].[CONTRACT END DATE])<(Date()+[Forms]![Contracts
Expiring]![txtNotify])));
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)
P.S. You deleted the Module1, but you didn't get rid of the references to the
GroupWise libraries!
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Thursday, June 02, 2011 7:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Code for Contracts Expiring
John -
I uploaded an updated database for you to look at. I took out the modules so
you don't get the errors for groupwise.
I added in the joins in the query. Can you look to see if I joined them
correctly?
Thanks!
Kat
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Kat-
>
> Is there anything in the txtNotify control on the Contracts Expiring form?
>
> And where are your joins in the query? You're getting the cartesian product
of
> Contract Information, Vendor Information, and Employee Input Information.
> That's why you're getting multiple rows.
>
> 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 ka0t1c_ang3l
> Sent: Thursday, June 02, 2011 4:21 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Code for Contracts Expiring
>
> John -
>
> This is the SQL for the query:
>
> SELECT [CONTRACT INFORMATION].[CONTRACT START DATE], [CONTRACT
> INFORMATION].[CONTRACT END DATE], [VENDOR INFORMATION].[VENDOR E-MAIL],
> [CONTRACT INFORMATION].[CONTRACT ID], [EMPLOYEE INPUT INFORMATION].[E-MAIL],
> [VENDOR INFORMATION].[CONTACT PERSON], [CONTRACT INFORMATION].[VENDOR ID]
> FROM [CONTRACT INFORMATION], [VENDOR INFORMATION], [EMPLOYEE INPUT
INFORMATION]
> WHERE ((([CONTRACT INFORMATION].[CONTRACT END
DATE])<(Date()+[Forms]![Contracts
> Expiring]![txtNotify])));
>
>
> Thanks!
>
> Kat
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Kat-
> >
> > What is the SQL for your Expired Contracts query?
> >
> > You really shouldn't open a table or query directly in an application. You
> > should open a filtered form instead.
> >
> > 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 ka0t1c_ang3l
> > Sent: Thursday, June 02, 2011 3:53 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Code for Contracts Expiring
> >
> > Good Morning!
> >
> > I'm having a problem with the code I have set up when I login into my
> database.
> > The code is supposed to let me know if there are any contracts expiring and
> then
> > asks if I would like to view them. When I click yes, it opens up a query
with
> > all the contracts due to expire; however, when the form opens it shows all
the
> > contracts I've entered with the Contract End Date of the contract that is
due
> to
> > expire. See the following:
> >
> > CONTRID CONTRSTARTDATE CONTRENDDATE CONTPERSON VENDOR ID
> > 4 2/4/2010 5/31/2010 Michelle Locke Art Museum
> > 4 2/4/2010 5/31/2010 Scott Cordero Art Museum
> > 4 2/4/2010 5/31/2010 Eduardo Perez Art Museum
> > 4 2/4/2010 5/31/2010 Capt. John Pasch Art Museum
> >
> > This indicates that all these contracts are for the Art Museum and expired
on
> > 5/31/2010, but only Michelle Locke is with the Art Museum and only this
> contract
> > expired on 5/31/2010.
> >
> > I don't know why it's doing that. How can I get it to only show those
> contracts
> > that are expiring for example if I enter 60 days, instead of showing me the
> > above.
> >
> > This is the code I have set up for this query:
> >
> > Private Sub Form_Load()
> > Call ShowHideButtons(Me)
> > If glngRank >= 100 Then
> > If Not IsNull(DLookup("[Contract ID]", "[CONTRACT INFORMATION]", "[CONTRACT
> END
> > DATE] <= #" & Date + 30 & "#")) Then
> > If vbYes = MsgBox("There are contracts expiring. Would you like to view
> them?",
> > vbQuestion + vbYesNo) Then
> > ' Open the contract expire form
> > DoCmd.OpenQuery "Expired Contracts"
> > DoCmd.Close acForm, "MAIN MENU"
> > End If
> > End If
> > End If
> > End Sub
> >
> > Any help is appreciated!
> >
> > Thanks!
> >
> > Kat
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar