Maybe I spoke to soon....
The query brings up all contact data if it = Asset data and the mobile phone number is the link from table to table.
What I wanted the query to do was list all Contacts and if there was an asset identifable by the mobile phone number then show that record from Assets.
How do I tell it to do this?
SELECT Contacts.ID, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.Department, Contacts.[Office Phone], Contacts.[Mobile Phone], Contacts.FaxNumber, Contacts.Supervisor, Assets.Unit, Assets.[Last Month], Assets.Current, Assets.CO, Assets.[Acc Code], Assets.[Well No], Assets.Model, Assets.Year, Assets.Description, Assets.[Vin#], Assets.License, Assets.Acquired, Assets.Weight, Assets.Supervisor, Assets.Images, Assets.Retired, Assets.Total, Assets.Sold, Assets.Notes
FROM Assets INNER JOIN Contacts ON Assets.[Mobile Phone] = Contacts.[Mobile Phone];
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 2:16 PM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
When I asked what your query looks like now, I should have been more specific. What is the SQL of the query??
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 cc0623
Sent: Monday, October 31, 2011 9:00 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
My query has only the data from the contacts table. I still need it to populate from the Assets table.
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 1:57 PM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
What does your query look like now? If you're using an INNER JOIN, you'll see only those Assets that have been assigned to a Contact.
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 cc0623
Sent: Monday, October 31, 2011 8:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
Asset detail shows detail of one asset and assets is a list of assets that person is responsible for in a data sheet view. some people have many assets they are responsible for. I set the primary key to Unit# in Assets and the primary key is ID in Contacts. So what do I do now? I am still only getting half the data in my query...
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 1:35 PM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
To link Contacts to the Assets for which they are responsible, you need an ID field in the Assets table that contains the Primary Key value of the related contact. I'm not sure what you mean by asset detail vs. asset, but you should not need three tabs. An outer form editing contacts and a subform with all the Assets for which the current person is responsible should do the trick.
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 cc0623
Sent: Monday, October 31, 2011 8:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
One person can be responsible for more than one asset, but there is never more than one person responsible for that asset at any given time. What I want to do is create a three tab form "contact" is primary, "asset detail" and "assets" then the tables will be updated from this multi tab form. I imagine this will require a query to get the information to update the two tables "Assets" & "Contacts".
I have managed to get the query to populate data from one of the tables, but not the other.
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 10:53 AM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
Are you saying there is no field that links the two tables? Is an Asset owned by one Contact, or can an Asset have multiple owners? If an Asset is owned by one Contact, then you need the ID field from Contacts in the Assets table to indicate who is the owner. If there can be multiple owners, then you need a third table to link the two.
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 cc0623
Sent: Monday, October 31, 2011 5:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
How do I make them related?
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 10:01 AM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
Well, I doubt very much that the Assets table is related to Contacts on Unit = LastName. How do you know which contact owns what asset? That's how you need to link the tables to make the query work.
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 cc0623
Sent: Monday, October 31, 2011 4:29 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
Hi John-
SELECT Assets.Unit, Assets.[Last Month], Assets.Current, Assets.CO, Assets.[Acc Code], Assets.[Well No], Assets.Model, Assets.Year, Assets.Description, Assets.[Vin#], Assets.License, Assets.Acquired, Assets.Weight, Assets.Supervisor AS Assets_Supervisor, Assets.Images, Assets.Images.FileData, [Assets].[Images].[FileFlags] AS Expr1, Assets.Images.FileName, [Assets].[Images].[FileTimeStamp] AS Expr2, Assets.Images.FileType, [Assets].[Images].[FileURL] AS Expr3, Assets.Retired, Assets.Total, Assets.Sold, Assets.Notes, Contacts.ID, Contacts.LastName, Contacts.FirstName, Contacts.EmailAddress, Contacts.Department, Contacts.[Office Phone], Contacts.MobilePhone, Contacts.FaxNumber, Contacts.Supervisor AS Contacts_Supervisor
FROM Assets INNER JOIN Contacts ON Assets.[Unit] = Contacts.[LastName];
Thanks for the help. I really appreciate it.
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 9:26 AM
Subject: RE: [MS_AccessPros] Query but no data?
Tiffany-
Open your query in Design view. Switch to SQL view and then copy and paste the text you see into a reply here. Whenever you have a problem with a query, it's always best to post the SQL to get better help.
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 cc0623
Sent: Monday, October 31, 2011 4:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
John what do you mean SQL of the query? I looked under SQL view and I see where are the column labels are.
Tiffany Atchley
PO Box 1003
Fort Duchesne, UT 84026
435-722-7833
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 8:29 AM
Subject: RE: [MS_AccessPros] Query but no data?
More specifically, what is the SQL of the query?
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 Jim Wagner
Sent: Monday, October 31, 2011 3:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?
How are the tables linked together? Are the linked fields in each table populated with data with no nulls?
Jim Wagner
________________________________
________________________________
From: cc0623 <cc0623@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, October 31, 2011 6:57 AM
Subject: [MS_AccessPros] Query but no data?
I ran a query and selected columns from three tables. The columns show, but the data did not. How do I make the data show up?
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar