Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Import query from access to excel, link to template, email on
I am analyzing a query with excel. In excel, I have created a template that
is linked to the query. I want to email that template on to others, but they are getting messages, unable to find source - unable to update link, etc. They can ignore errors and receive the worksheet without errors. Is there a way to send info from access to email users while retaining the format and having some fields protected? Then, they can enter info into unprotected fields and email back to me. Any suggestions? Thanks in advance, JR |
#2
|
|||
|
|||
You probably want to make the query a dead copy of the data rather than
saving the query definition. Put the cursor in the query output table and Data / Import External Data / Data Range Properties ... uncheck Save Query Definition. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Could you be more specific as to how I accomplish this? Thank you.
"Bill Manville" wrote in message ... You probably want to make the query a dead copy of the data rather than saving the query definition. Put the cursor in the query output table and Data / Import External Data / Data Range Properties ... uncheck Save Query Definition. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
I don't know how to be more specific than to say again:
Put the cursor in the query output table and Data / Import External Data / Data Range Properties ... uncheck Save Query Definition. I am assuming that you are using Excel 2002 or later (it always helps if you say which version) and that the data was brought in from Access by Data / Import External Data / New database query. If this is not the case then you will have to help me by giving me more information about what you did. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
Bill - I just realized that you and I had communicated previously regarding
this situation. I was unable to import data from access because I get to the point where excel ask if I want to view with MSQuery, (2 other questions) and a box to check if I want to save query. I did not check Save Query (did not see exact phrase - save query definition) and I get error that it was looking for parameters. The query has parameters in access and it works without problems in access. I do not know what to do about parameters once I get to the importing procedure from access to excel. Excel 2002 version Thanks "Bill Manville" wrote in message ... I don't know how to be more specific than to say again: Put the cursor in the query output table and Data / Import External Data / Data Range Properties ... uncheck Save Query Definition. I am assuming that you are using Excel 2002 or later (it always helps if you say which version) and that the data was brought in from Access by Data / Import External Data / New database query. If this is not the case then you will have to help me by giving me more information about what you did. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
|
|||
|
|||
We may be misunderstanding each other a bit.
Let's start again. Jwr wrote: I am analyzing a query with excel. In excel, I have created a template that is linked to the query How is it "linked"? From your latest message it seems it might not be by the usual Data / Import External Data / New Database Query mechanism that I had been assuming. If you have somehow created a DDE link to the results of the Access query (and I admit I didn't know that was possible) then you may be able to kill the link by Edit / Links / Break Link. If not, try Edit / Copy; Edit / Paste Special / Values. The query has parameters in access and it works without problems in access. I do not know what to do about parameters once I get to the importing procedure from access to excel. I haven't tried importing from an Access query with parameters. I know how to create an MSQuery query with parameters. At the end of the query wizard, elect to view the query with MS Query. Display the conditions grid. Enter the field name in the top row of the conditions grid. Enter a prompt like [Which City?] in the second row. Execute the query - you should be prompted for the parameter. In that way you could change the Access query not to have parameters and supply the parameters at the MSQuery stage. Back in Excel there are options to get the parameter value from a cell and even to refresh the query when the cell value changes. I did not check Save Query (did not see exact phrase - save query definition) If you mean the Save Query... button on the last part of the query wizard, that is a different type of save, I think. It saves the query in a text file. When you get back to Excel you get a dialog headed Import Data asking where you want to put the results. On this dialog if you click Parameters you can tell it where to get the parameter value from, and if you click Properties you will see the dialog in which you might later uncheck the Save Query Definition box to make the query non-refreshable. Are we getting closer? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#7
|
|||
|
|||
1.In access, I created a query, then tools/office links/analyze with
Microsoft excel. That information is saved in excel. I then created a template in excel and copied/paste special/ paste link for each cell where I wanted information. a. I have created a copy of this template information linked to the query/paste special/paste values only. This works except it is very time consuming and leaves room for many many errors. I do not think that this will work in my situation; i.e. I would have had to "paste values" in approximately 250 templates in the month of Setpember and there are about 50 fields to be copied. 2.I tried again to Data/Import External Data/New Database Query. I get to the point of viewing the query with MS Query. At that point, I am asked about parameters. I can click ok several times and the screen will go to the MSQuery page, but when I attempt to put a field on the grid, the table selection at the top disappears and I cannot enter anything. I have tried about 10 times just in case I was making the wrong selections. 3.I can open a new excel worksheet/data/import external data/import data and select my access database. This brings over my tables and queries, but only queries that have no parameters. Sorry if I am not making myself clear. I appreciate your assistance. Joy "Bill Manville" wrote in message ... We may be misunderstanding each other a bit. Let's start again. Jwr wrote: I am analyzing a query with excel. In excel, I have created a template that is linked to the query How is it "linked"? From your latest message it seems it might not be by the usual Data / Import External Data / New Database Query mechanism that I had been assuming. If you have somehow created a DDE link to the results of the Access query (and I admit I didn't know that was possible) then you may be able to kill the link by Edit / Links / Break Link. If not, try Edit / Copy; Edit / Paste Special / Values. The query has parameters in access and it works without problems in access. I do not know what to do about parameters once I get to the importing procedure from access to excel. I haven't tried importing from an Access query with parameters. I know how to create an MSQuery query with parameters. At the end of the query wizard, elect to view the query with MS Query. Display the conditions grid. Enter the field name in the top row of the conditions grid. Enter a prompt like [Which City?] in the second row. Execute the query - you should be prompted for the parameter. In that way you could change the Access query not to have parameters and supply the parameters at the MSQuery stage. Back in Excel there are options to get the parameter value from a cell and even to refresh the query when the cell value changes. I did not check Save Query (did not see exact phrase - save query definition) If you mean the Save Query... button on the last part of the query wizard, that is a different type of save, I think. It saves the query in a text file. When you get back to Excel you get a dialog headed Import Data asking where you want to put the results. On this dialog if you click Parameters you can tell it where to get the parameter value from, and if you click Properties you will see the dialog in which you might later uncheck the Save Query Definition box to make the query non-refreshable. Are we getting closer? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#8
|
|||
|
|||
OK.
I have never used the Analyze with Excel feature in Access but I have met a number of people in newsgroups who have had problems with it. I always start from the Excel end and build the query in MSQuery. The result is always good. Would it be too difficult to rebuild the Access query in MSQuery? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#9
|
|||
|
|||
Probably not; however, I have never done this. What would be the proper
procedure? The main thing is that the query/template must be updateable as access info change. Can you give me some direction to attempt this? Thanks in advance. Joy "Bill Manville" wrote in message ... OK. I have never used the Analyze with Excel feature in Access but I have met a number of people in newsgroups who have had problems with it. I always start from the Excel end and build the query in MSQuery. The result is always good. Would it be too difficult to rebuild the Access query in MSQuery? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#10
|
|||
|
|||
Can you post the SQL of the Access query?
And confirm the version of Office that you are using? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#11
|
|||
|
|||
XP Pro/Excel 2002/Microsoft Office 2003
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress, Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem, [Order Details].Quantity, Products.ProductCode, Products.ProductName, Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice, [Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price], IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In], IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS [Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #], [JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice Amount]*Products!HandlingPct AS [Handling $] FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID = Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE (((Customers.ControlNum)=[forms]![ParamControl#]![Beginning ControlNum] And (Customers.ControlNum)<=[forms]![ParamControl#]![Ending ControlNum])); "Bill Manville" wrote in message ... Can you post the SQL of the Access query? And confirm the version of Office that you are using? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#12
|
|||
|
|||
Quite a big query to redefine in MSQuery from scratch.
What I suggest you do is use Data / Import External Data / New Database Query to define a simple query on the same database. After returning results to Excel, right-click the results area and Edit Query. If it doesn't take you direct to the MSQuery query grid, click Next until you reach the end of the wizard and then select to edit the query in MS Query. In MS Query click the SQL button and then paste this slightly modified version of your query into the SQL window replacing what was there before. SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress, Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem, [Order Details].Quantity, Products.ProductCode, Products.ProductName, Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice, [Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price], IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In], IIf([Total Price]<0,[Total Price],Null) AS [Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS [Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #], [JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice Amount]*Products!HandlingPct AS [Handling $] FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID = Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE Customers.ControlNum=[First Control Number?] And Customers.ControlNum<=[Last Control Number?] If you now try to run the query by clicking the ! button it might flag some syntax it doesn't like - in which case give me details and I'll try to help you fix it - or it will prompt you for the 2 parameters (First Control Number and Last Control Number). File / Return results to Excel. Then when you refresh the query in Excel it should prompt you again for the parameters. As mentioned before you can link the parameters to cells if you wish. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Query Linked to Excel fixed rows | Links and Linking in Excel | |||
Link Access table to Excel worksheet | Excel Discussion (Misc queries) | |||
link Access workbook to Excel workbook | Excel Discussion (Misc queries) | |||
Trying to link MS Access to MS Excel | Links and Linking in Excel | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) |