Senin, 31 Oktober 2011

RE: [MS_AccessPros] Query but no data?

 

Ooops. Did that backwards! Try 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 RIGHT JOIN Contacts ON Assets.[Mobile Phone] = Contacts.[Mobile Phone];

When you go back to Design view, you should see an arrow on the join line pointing from Contacts to Assets.

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 John Viescas
Sent: Tuesday, November 01, 2011 7:32 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Query but no data?

Tiffany-

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 LEFT JOIN Contacts ON Assets.[Mobile Phone] = Contacts.[Mobile Phone];

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 10:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?

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]

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

[MS_AccessPros] Report Sort Problem

 

Dear All,

I have made a report based on a query, the report have not any sort order neither the query but, when I preview the report, its detail section is sort descending, how I remove this sort order. 
 
Thanks & Regards,
--
Abdul Shakeel

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

RE: [MS_AccessPros] Database Corruption Problems

 

David-

Please explain what you mean by "click out of programs". Is "programs" another form in the app? Do you have any code running in GotFocus?

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 David Tolson
Sent: Tuesday, November 01, 2011 3:00 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Database Corruption Problems

John, the blow-ups are not as frequent, but there does seem to be a pattern here. Access just stops working when I click out of programs and go back to the main menu. It is not a switchboard, but a form that I made to mimic a switchboard. When I get back to frmMenuMaster, Access stops working.

r/David

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Monday, October 31, 2011 2:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Database Corruption Problems

David-

Finding and fixing the compile error should have cleaned it up. If you're still having problems, try importing into a new database, do a compile and save, and then compact/repair.

Good luck!

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%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David Tolson
Sent: Monday, October 31, 2011 7:32 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Database Corruption Problems

Thank you John, I have tried your suggestions, and although I did have one compile issue, after correcting it, I am still having the problems, and I do not have any Missing references.

Thanks,

David

From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John Viescas
Sent: Monday, October 31, 2011 10:30 AM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Database Corruption Problems

David-

Open the VB Editor and choose References from the Tools menu. Do you see any libraries marked "Missing"? If so, that's your problem. If not, then does the project compile? (Debug - Compile) Compile errors can cause those messages.

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%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Jim Wagner
Sent: Monday, October 31, 2011 2:58 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Database Corruption Problems

David,

This happens to my boss on several databases we support every time she opens it. But on my computer, I have no issues. We have never figured out why. Have you tried opening the database on another computer?

Jim Wagner
________________________________

________________________________
From: djsdaddy531 <drtolson@verizon.net <mailto:drtolson%40verizon.net> <mailto:drtolson%40verizon.net> >
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Monday, October 31, 2011 6:49 AM
Subject: [MS_AccessPros] Database Corruption Problems

Great Day, the Access application that I am creating appears to be
corrupted BIG TIME. I am randomly receiving the error message…

The expression On Click you entered as the event property setting
produced the following error: User defined type not defined.

Or sometimes the application just stops working

Now, I have received these errors in the past, and either a Compact and
Repair or creating a new database and transferring all of the objects
would normally correct it. This time neither process has corrected the
problem. I even tried to do some maintenance on my disk and that has not
corrected the problem, and I have also split the database.

Any suggestions on how to remedy this problem will be greatly
appreciated.

Thanks in advance,

David

[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

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

RE: [MS_AccessPros] UNION query "Too Complex"

 

Robin-

If the characters just before the "name" you want to extract are always
different, then my technique won't work. I was assuming that "au/clubs/atoc/"
was always the identifying string just ahead of the club name per your one query
example. It's clear now that you are custom building each query depending on
the name you are looking for! Follow Duane's advice.

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 Robin Chapple
Sent: Monday, October 31, 2011 11:32 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] UNION query "Too Complex"

John,

Thanks for the suggestion. As you know I am not 'guru'. Before I
start on the project please look at a sample of the imported data
because I see the function counts characters and the number of
characters in front of the preamble differs vastly. I cannot change this:

StatsYTD
Page Hits
The Rotary Foundation (http://www.rotary9790.org.au/4a/comm/trf.asp) 695
http://www.rotary9790.org.au/dbs/db8/members/membersbyclubsrch.asp 1375
Rotary Club of Diamond Creek Home Page
(http://www.rotary9790.org.au/clubs/dtok/diamondcreek/) 697
http://www.rotary9790.org.au/dbs/members/ 1132
http://www.rotary9790.org.au/clubs/atoc/coburg/market.asp 646

Regards,

Robin

At 31/10/2011 05:07 PM, you wrote:
>Or just write a function:
>
>Public Function FindGroupName(strURL As String) As String
>Dim intI As Integer, strWork As String
>
> ' Find the start of the search string:
> intI = InStr(strURL, ".au/clubs/atoc/")
> ' Strip off the front end
> strWork = Mid(strURL, intI + 15)
> ' find the ending "/"
> intI = InStr(strWork, "/")
> ' Return the name
> FindGroupName = Left(strWork, intI - 1)
>End Function
>
>Now write your query:
>
>SELECT FindGroupName(qStatsYTD.Page) As ClubName, Sum(qStatsYTD.Visitors)
>FROM qStatsYTD
>GROUP BY FindGroupName(qStatsYTD.Page)
>
>
>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 Duane Hookom
>Sent: Monday, October 31, 2011 12:48 AM
>To: Access Professionals Yahoo Group
>Subject: RE: [MS_AccessPros] UNION query "Too Complex"
>
>
>Robin,
>That's what I was afraid of. I'm not sure why you break the table
>apart and then
>attempt to put it back together again.
>
>You could just store values of folder names like
>"au/clubs/atoc/Albury/" with a
>title field in another table and build a totals query. Set the criteria under
>Page to
>Like "*" & [Your Folder Name Field] & "*"
>
>Group by [Your folder Title field].
>
>This solution uses one extra table of data that puts maintenance where it
>belongs (in your table, not in code or expressions). You can add, remove, or
>edit records as you modify your website pages.
>
>Don't work so hard. Life is too short.
>
>Duane Hookom
>MS Access MVP
>
>
>
>
>
>To: MS_Access_Professionals@yahoogroups.com
>From: robinski@mymail.net.au
>Date: Mon, 31 Oct 2011 09:58:45 +1100
>Subject: RE: [MS_AccessPros] UNION query "Too Complex"
>
>
>
>
>
>
>
>They are analysis of pages counted from a website. All from the same table.
>
>Each query counts pages visited in a given folder.
>
>Two examples:
>
>SELECT qStatsYTD.StatsID, qStatsYTD.Page, qStatsYTD.Visitors
>FROM qStatsYTD
>WHERE (((qStatsYTD.Page) Like "*.au/clubs/atoc/Albury/*"));
>
>SELECT qStatsYTD.StatsID, qStatsYTD.Page, qStatsYTD.Visitors
>FROM qStatsYTD
>WHERE (((qStatsYTD.Page) Like "*.au/clubs/atoc/AlburyHume/*"));
>
>Robin
>
>At 31/10/2011 09:27 AM, you wrote:
>
> >I wonder why there are 46 query/tables that are so similar they need
> >to be union'd. Are they all similar queries from the same tables?
> >
> >Duane Hookom
> >MS Access MVP
> >
> >
> >
> >
> >To: MS_Access_Professionals@yahoogroups.com
> >From: john@viescas.com
> >Date: Sun, 30 Oct 2011 22:26:24 +0100
> >Subject: RE: [MS_AccessPros] UNION query "Too Complex"
> >
> >
> >
> >
> >
> >
> >Robin-
> >
> >You could try breaking it into three or four groups of UNION
> >queries, then UNION
> >the UNIONs. If that fails, you'll have to break it into pieces and
> then use an
> >Append query to dump the results into a table that you can then feed to the
> >report.
> >
> >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 Robin Chapple
> >Sent: Sunday, October 30, 2011 10:14 PM
> >To: MS_Access_Professionals@yahoogroups.com
> >Subject: RE: [MS_AccessPros] UNION query "Too Complex"
> >
> >Thanks John,
> >
> >I read the Microsoft Page on the subject.
> >
> >That was unknown to me but I still get the error message.
> >
> >The are 46 UNION statements.
> >
> >Cheers,
> >
> >Robin
> >
> >At 30/10/2011 06:06 PM, you wrote:
> > >Robin-
> > >
> > >Yes, roughly 16 UNION statemetns. Try using UNION ALL to eliminate the
> > >unnecessary sort of the records.
> > >
> > >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
> Robin Chapple
> > >Sent: Sunday, October 30, 2011 1:22 AM
> > >To: MS_Access_Professionals@yahoogroups.com
> > >Subject: [MS_AccessPros] UNION query "Too Complex"
> > >
> > >I am using Access 2007 to prepare a statistical report from visits to
> > >a web site.
> > >
> > >I have planned a report with counts of visitors per folder. The query
> > >that delivers the result is a UNION query and after I added the
> > >latest folder count an error message was generated that the query was
> > >"Too complex".
> > >
> > >Here is the first part of the SQL:
> > >
> > >SELECT "Bulletins" AS Visitors, TotalBulletins.SumOfVisitors
> > >FROM TotalBulletins;
> > >
> > >UNION SELECT "Conference Pages" AS Visitors, TotalConf.SumOfVisitors
> > >FROM TotalConf;
> > >
> > >UNION SELECT "Club Pages Combined" AS Visitors, TotalClub.SumOfVisitors
> > >FROM TotalClub;
> > >
> > >UNION SELECT "Clubs - Diamond Creek Pages" AS Visitors,
> > >TotalDiamondCreek.SumOfVisitors
> > >FROM TotalDiamondCreek;
> > >
> > >Is there an Access limit to how many I may use?
> > >
> > >Many thanks,
> > >
> > >Robin Chapple
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> >
> >------------------------------------
> >
> >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
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

RE: [MS_AccessPros] Query but no data?

 

Tiffany-

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 LEFT JOIN Contacts ON Assets.[Mobile Phone] = Contacts.[Mobile Phone];

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 10:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query but no data?

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]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Re: [belajar-access] Re: Report list

 

Mas Robbi,
 
Kalau ga keberatan disertakan contohnya agar bisa ditangkap lebih jelas maksudnya.
Thanks
 
 

From: "robbi_ansyah@yahoo.com" <robbi_ansyah@yahoo.com>
To: Rumag access millis <belajar-access@yahoogroups.com>
Sent: Tuesday, November 1, 2011 12:29 PM
Subject: [belajar-access] Re: Report list

Powered by Telkomsel BlackBerry®

-----Original Message-----
From: robbi_ansyah@yahoo.com
Date: Tue, 1 Nov 2011 05:28:08
To: <rumah>
Reply-To: robbi_ansyah@yahoo.com
Subject: Report list

contohnya..

---------------------------------------------------
No. Kode barang.        Nama barang
---------------------------------------------------
1.    OO1.                  Abcdfg hijklmn
    XXXX  XXXX.  XXXX. XXXX
    XXXX  XXXX.  XXXX. XXXX
    XXXX  XXXX.  XXXX. XXXX
---------------------------------------------------
Ini format list yg saya ingin kan, sementara list ini data'a ada dalam satu filds...

Salam bangga buat semua'a
Trims

Powered by Telkomsel BlackBerry®

------------------------------------

SPAM IS PROHIBITEDYahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/belajar-access/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/belajar-access/join
    (Yahoo! ID required)

<*> To change settings via email:
    belajar-access-digest@yahoogroups.com
    belajar-access-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    belajar-access-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/



__._,_.___
Recent Activity:
SPAM IS PROHIBITED
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___