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]
Tidak ada komentar:
Posting Komentar