Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 6th 05, 02:31 AM
jwrnana
 
Posts: n/a
Default Help - first time to attempt link

Can anyone direct me to a site or give some direction in linking an access
database with excel? I have attempted getting external data - creating a
new database in excel - but all of my queries, tables, etc. are not being
imported into excel!

Thanks, JR



  #2   Report Post  
Old September 6th 05, 07:52 AM
Bill Manville
 
Posts: n/a
Default

Can you give us a better idea of what you are attempting to do?
Also mention which version of Office you are using, please.

The most usual way of "linking" Excel and Access is to run queries in
Excel (Data / Import External Data / New Database Query) to import
selected information from an Access database into an Excel worksheet
where it can be referenced using formulas (e.g. VLOOKUP) elsewhere in
the worksheet.

If you have information in Excel that you want to make available to the
Access database you can create a table in Access that is linked to the
Excel file (File / Get External Data / Link Tables), but I would not
suggest using that as the long term way of dealing with your data -
usually better to import it into Access and keep it there.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
Old September 6th 05, 02:00 PM
jwrnana
 
Posts: n/a
Default

I have an Order Entry database. Once an order is shipped, I need to
forward all of the following: customer info, order info, ship to
info, product info, order date, invoice date, ship date. I have a
query that contains all of this information and it works fine in
access; however, I need to email the query, some may
have access, others not. Thus, I need to export access database query to
excel.

Going directly from access to excel (analyze with excel), the information
flows through, but the
format is "jumbled." I have to decrease size of rows and columns to
make it legible. Then I need to protect some of the columns as the
end user is to input information into several columns of the
worksheet and email back to me. I then must bring that information
"back" to access.

As you can see, I really do not know which way to go with this. When
I import database query to excel, I do not see all of my queries - in
particular the one that I am needing.

I was thinking a template might work, but if I cannot get all of my
database to excel, it probably will not.

I have windows XP and Excel 2000.

Thanks you




"Bill Manville" wrote in message
...
Can you give us a better idea of what you are attempting to do?
Also mention which version of Office you are using, please.

The most usual way of "linking" Excel and Access is to run queries in
Excel (Data / Import External Data / New Database Query) to import
selected information from an Access database into an Excel worksheet
where it can be referenced using formulas (e.g. VLOOKUP) elsewhere in
the worksheet.

If you have information in Excel that you want to make available to the
Access database you can create a table in Access that is linked to the
Excel file (File / Get External Data / Link Tables), but I would not
suggest using that as the long term way of dealing with your data -
usually better to import it into Access and keep it there.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #4   Report Post  
Old September 7th 05, 07:29 AM
Bill Manville
 
Posts: n/a
Default

A better solution might be a web page front end to your database that
the users could use directly to enter the required information.

If you want to do it via Excel then I would use Data / Get External
Data / New Database Query, and in MSQuery I would select * from the
relevant query from the database.

You could then run a macro to refresh the query and protect the data as
necessary.

Feeding data back to Access is not quite so straightforward.
I would construct an INSERT INTO query and execute it via a DAO
connection to the database.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #5   Report Post  
Old September 8th 05, 12:30 AM
jwrnana
 
Posts: n/a
Default

I used Data/Get External Data/New Database Query, and all of my information
did not come over to excel. My tables did, and some of my queries; however,
the query that I was particularly interested in in getting to excel did not.
Instead, I am seeing MSsysqueries, etc.


"Bill Manville" wrote in message
...
A better solution might be a web page front end to your database that
the users could use directly to enter the required information.

If you want to do it via Excel then I would use Data / Get External
Data / New Database Query, and in MSQuery I would select * from the
relevant query from the database.

You could then run a macro to refresh the query and protect the data as
necessary.

Feeding data back to Access is not quite so straightforward.
I would construct an INSERT INTO query and execute it via a DAO
connection to the database.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup





  #6   Report Post  
Old September 8th 05, 01:46 PM
Bill Manville
 
Posts: n/a
Default

So what is different about the query that you wanted to see?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #7   Report Post  
Old September 8th 05, 02:29 PM
jwrnana
 
Posts: n/a
Default

Following is the query that I have in access that did not export to Get
External Data/New Database/MSQuery. Some of my queries (very small - 4-5
fields) did however. This is the query that I need to email on to others.


I also attempted to create a template from one I analyzed with Office
Links/Microsoft Excel, but I got errors saying my formula was wrong and all
I was doing was putting in cells that had no formula; i.e. names, addresses.




SELECT Orders.OrderDate, Contracts.ContractNum, Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [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, [Order Details].OrderID, ([Amount Before
Trade-In]*0.95) AS [Dealer 95%], 0.5*[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;

"Bill Manville" wrote in message
...
So what is different about the query that you wanted to see?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #8   Report Post  
Old September 9th 05, 12:12 AM
Bill Manville
 
Posts: n/a
Default

I pasted your query into an Access database of mine.
Obviously it would not work as I don't have your tables, but it did appear in
the list of available views in MSQuery (make sure you have Views selected in
the Options dialog from the MSQuery wizard).

It is unusual in my experience to use one field that you name in the query in
the definition of another field and I wonder whether that might be a cause of
a problem.

I would have written it as

SELECT Orders.OrderDate, Contracts.ContractNum, Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [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([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Amount Before Trade-In],
IIf([Order Details]!UnitPrice*[Order Details]!Quantity<0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Trade-In Amount], [Order
Details].OrderID, Orders.ShipDate, [Order Details].OrderID, (IIf([Order
Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null)*0.95) AS [Dealer 95%],
0.5*IIf([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) 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;

Incidentally, I am surprised by the 0.5 in that query - should it not be 0.05?

You can always reconstruct the query in MSQuery if you are having trouble
accessing its Access definition.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #9   Report Post  
Old September 9th 05, 07:18 PM
jwrnana
 
Posts: n/a
Default

I pasted your SQL query in place of mine. It would not run because of
error - "improper bracketing of John Deere Invoice".

Thanks
JR
"Bill Manville" wrote in message
...
I pasted your query into an Access database of mine.
Obviously it would not work as I don't have your tables, but it did appear

in
the list of available views in MSQuery (make sure you have Views selected

in
the Options dialog from the MSQuery wizard).

It is unusual in my experience to use one field that you name in the query

in
the definition of another field and I wonder whether that might be a cause

of
a problem.

I would have written it as

SELECT Orders.OrderDate, Contracts.ContractNum,

Orders.PurchaseOrderNumber,
Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName,
Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince,
Orders.ShipPostalCode, Orders.ShipCountry, [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([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Amount Before

Trade-In],
IIf([Order Details]!UnitPrice*[Order Details]!Quantity<0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) AS [Trade-In Amount],

[Order
Details].OrderID, Orders.ShipDate, [Order Details].OrderID, (IIf([Order
Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null)*0.95) AS [Dealer 95%],
0.5*IIf([Order Details]!UnitPrice*[Order Details]!Quantity=0,[Order
Details]!UnitPrice*[Order Details]!Quantity,Null) 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;

Incidentally, I am surprised by the 0.5 in that query - should it not be

0.05?

You can always reconstruct the query in MSQuery if you are having trouble
accessing its Access definition.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #10   Report Post  
Old September 10th 05, 01:10 AM
Bill Manville
 
Posts: n/a
Default

Jwrnana wrote:
I pasted your SQL query in place of mine. It would not run because of
error - "improper bracketing of John Deere Invoice".


OK.
Probably not an accurate transcription of the error, but there are 2
reasons why what I sent might not work:
a) line breaks inserted in the forum message
b) I didn't spot some instances of fields defined in the output list
being used in other output fields, e.g. [JD Invoice Amount]

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I link multiple cells at the same time? Juippi Excel Worksheet Functions 1 June 16th 05 06:17 PM
Time Sheet Calculation Help Needed! sax30 Excel Worksheet Functions 2 April 26th 05 08:08 PM
mulitiplying time Brad Excel Worksheet Functions 1 February 1st 05 07:15 PM
Calculating tvl time in Excel 2000 Rev.9.2720 Brandi Excel Worksheet Functions 5 January 27th 05 09:15 PM
entering numbers to display a time format Ian Williams/Eazygig Excel Discussion (Misc queries) 1 January 10th 05 11:57 AM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017