Excel query from Access databases

Hi,

I have two separate databases in Access that have a common field of CustNo. 
I'll like
to run a query in Excel using Get External Data... whereby I could create a
report that brings in data from both the databases.

Having tried this in Excel, it seems that I can only use one table at a
time, is there a way to overcome the issue.

Thanks, Rob



0
anonymous (74722)
4/14/2005 8:08:43 PM
excel 39879 articles. 2 followers. Follow

2 Replies
633 Views

Similar Articles

[PageSpeed] 3

Hi Rob-

"I have two separate databases in Access that have a common field of
CustNo."

Is that accurate or do you mean 2 separate Tables in 1 Access DB file which
use CustNo as the common field?

AFAIK you can only query one Access file at a time and only one Record
Source at a time from that file. The _Record Source_, however, does *not*
have to be a Table.

One idea:

(If 2 separate DB _Files_) In either of the DB files create a Link to the
Table in the other DB. Establish a Query Join (not a Relationship) using the
common field you refer to as a part of a Query in the DB where the Link is.

(If 2 _Tables_ in the same file) You can probably just make the Access query
as the tables are most likely related already with CustNo as the common
field.

Then use Excel to query that DB and use the Access query as the record
source.

You may also be able to work from within Access and Export the data found by
the Access query to an Excel file as another option.

HTH |:>)


On 4/14/05 4:08 PM, in article OAxUB3SQFHA.2932@TK2MSFTNGP09.phx.gbl, "Rob"
wrote:

> Hi,
> 
> I have two separate databases in Access that have a common field of CustNo.
> I'll like
> to run a query in Excel using Get External Data... whereby I could create a
> report that brings in data from both the databases.
> 
> Having tried this in Excel, it seems that I can only use one table at a
> time, is there a way to overcome the issue.
> 
> Thanks, Rob
> 
> 
> 

-- generaltaz1@comSpaMcast.net


0
xyz410 (17)
4/14/2005 10:22:04 PM
Thanks for the reply, the data is in 2 separate Access mdb files.  I will 
try to create the link in Access and  from within Access export the info.

Thanks again for the ideas.  Rob
"CyberTaz" <xyz@zzz.net> wrote in message news:BE84654C.58C2%xyz@zzz.net...
> Hi Rob-
>
> "I have two separate databases in Access that have a common field of
> CustNo."
>
> Is that accurate or do you mean 2 separate Tables in 1 Access DB file 
> which
> use CustNo as the common field?
>
> AFAIK you can only query one Access file at a time and only one Record
> Source at a time from that file. The _Record Source_, however, does *not*
> have to be a Table.
>
> One idea:
>
> (If 2 separate DB _Files_) In either of the DB files create a Link to the
> Table in the other DB. Establish a Query Join (not a Relationship) using 
> the
> common field you refer to as a part of a Query in the DB where the Link 
> is.
>
> (If 2 _Tables_ in the same file) You can probably just make the Access 
> query
> as the tables are most likely related already with CustNo as the common
> field.
>
> Then use Excel to query that DB and use the Access query as the record
> source.
>
> You may also be able to work from within Access and Export the data found 
> by
> the Access query to an Excel file as another option.
>
> HTH |:>)
>
>
> On 4/14/05 4:08 PM, in article OAxUB3SQFHA.2932@TK2MSFTNGP09.phx.gbl, 
> "Rob"
> wrote:
>
>> Hi,
>>
>> I have two separate databases in Access that have a common field of 
>> CustNo.
>> I'll like
>> to run a query in Excel using Get External Data... whereby I could create 
>> a
>> report that brings in data from both the databases.
>>
>> Having tried this in Excel, it seems that I can only use one table at a
>> time, is there a way to overcome the issue.
>>
>> Thanks, Rob
>>
>>
>>
>
> -- generaltaz1@comSpaMcast.net
>
> 


0
anonymous (74722)
4/15/2005 5:31:19 PM
Reply:

Similar Artilces:

Help
Good morning, I wonder if anyone can offer some suggestions of whatmight be going on here.I have an access db/program that has a from that is filled in, andthat form has some sub-forms. This has been working fine for months.All of a sudden, when we go to type into one of the sub-forms (any ofthe subforms - there are 3 on the form), we are getting this error:A little box with "Microsoft Visual Basic in the blue at the top) popsup with "error in loading DLL"I think this is where it's getting stuck at because this comes upyellow when it starts debug (Private Sub Form_KeyPress....

Restore Live Databases (System & Company) to a Test Database in a New Instance
I know how to restore live databases (System & Company) to test databases in a new instance. My problem is, I don't get to successfully open my Great Plains. I know that there are tables that I have to update (probably for the instance name). Can you help me with these tables? Check that your ODBC DSN is pointing to the correct server/instance. You do not need to do anything to just login as sa. However, you will still have to create the users in the new server. There is a techknowledge article on how to do this. I also have an SQL Script that will create the users for y...

Fatal Error: Language database file C:\PROGRA~1\FRXFIN~1\FRXFIN~1.
I recently had a user experience the following error when generating a report and thought I would share my fix in case any one else runs into this issue. Run-time error '-2147220503 (800403e9)' Fatal Error: Language database file C:\PROGRA~1\FRXFIN~1\FRXFIN~1.5\FRxXML4.dat not found. Also Received this error: Error 59999 Application-defined or object-defined error To fix this issue I reran the setup.exe and it corrected the issue on my computer on the users I had to run it twice and it corrected it. Troy, Interesting! We recently had a similar issue and if I recall correctly ...

Access 2007 calculation help
i have 2 fields in a table, 'qty on order' and 'qty rxd', In a query I subtract 'qty rxd' from 'qty on order' how can i store the result of this calculation, so the next time the calculation is carried out it subtracts the 'qty rxd' entered by the user from the result of the previous calculation. The result is also to be shown on a form. Hi, you can use an update query to put the calculated result in the field [qty on order], but I think you'd better consider to create a history table, related 1 to many to the table with the 2 qty fi...

Installer for my access application
Hello, All! I searching for installer for my program. It must support creating link like "path to msaccess.exe" "path to myprog.mde" /wrkgrp "path to users.mde" Is anybody know best solution for this??? Thank you in advance. With best regards, Alek Luchnikov. E-mail: alekluch---FORSPAMERS---1983@mail.ru ================================================= Look at Inno setup. It's free. "Alek Luchnikov" <alekluch---FORSPAMERS---1983@mail.ru> wrote in message news:um$uMd7dIHA.4728@TK2MSFTNGP03.phx.gbl... > Hello, All! > I searching f...

Trashing Accessing
Does anyone know if MS is planning on trashing Access dev and if plans are in the works for the future to stop dev with it. I am not privy to Microsoft's plans, but I have met people who are working on the next version of Access. On Thu, 29 Jan 2004 09:09:16 -0800, "Joshua Vance" <anonymous@discussions.microsoft.com> wrote: >Does anyone know if MS is planning on trashing Access dev >and if plans are in the works for the future to stop dev >with it. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. Well ... Microsoft i...

Multiple user access
I have XP prof, and outlook 2002 and would like to be able to allow someone else to access my outlook files when they are logged on as a different user. I have no "delegates" tab on my options menu. Do I need a different version of outlook? Any help appreciated. No, all you need is to put your .pst files in a location that's available to everyone who uses that machine, i.e. not in your Documents and Settings/%username%/ hierarchy. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers ...

Database Integration: which would work better?
I have a database that I am looking to use where the data contained needs to be accessible on a read-only basis through a web page. I have already gotten one Data Access Page created (and working pretty well). But I cannot seem to get the next page working as I am wondering if there could be something interfering with the relationships. So I am wondering if it would be easier to create this page in FrontPage 2003 or try and troubleshoot the problems I am having in trying to make this a Data Access Page. In the opinion of the community, which would be easier? So far I have tried this...

XML transform using XSL stored in database
I have an XML document that is transformed using XSL that is stored in a database. It works fine when the XSL is in a file, however when it comes from the database, I only see the text in the XSL (like to column headings) without any formatting. Here is the relevant code: SQLconn.Open(); SqlCommand SQLcmdc = new SqlCommand("SELECT xsl FROM xsl", SQLconn); SqlDataReader dRsltc = SQLcmdc.ExecuteReader(); dRsltc.Read(); string sXml = dRsltc["xsl"].ToString(); SQLconn.Close(); StringReader sr = new StringReader(sXml); xPathDoc = new XPathDocument(sr); xslt.Load(xPathDoc,nul...

Finding a string in Excel cells
Not sure if this is an Excel problem or a general Microsoft products "feature", but here goes:- I've inherited a HUGE spreadsheet with 6 figures of cells in total(!) and I'm told that some have been highlighted for query or error with "???" in the cell (in addition to other text in that cell). Trouble is, as soon as I do "find" with the "?" character, it seems to ignore it, as if ? is some reserved character. Does anyone have any idea how I can get round this? Ta! The ? character is used as a wild card character (any single character). ...

exporting email to access table
Is there a way to export emails to a microsoft Access database table? any direction would be great. thanks, mate ...

Excel formula help needed!
Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the st...

cut and paste from explorer window into excel
I need help! How can I cut and paste several files (hundreds of them)from an Explorer window into an Excel spreadsheet without having to do them one at a time. Thanks, ...

Excel 2007 chart gridline color using vba
Hello, i want to apply a themecolor to a chart gridline using vba. I can apply normal colors (RGB, index) but not the theme color. Any idea? thx. berg Hi, Here are the various ways fo changing the colour. With ActiveChart With .Axes(xlValue, xlPrimary) If .HasMajorGridlines Then With .MajorGridlines .Border.ColorIndex = 5 'set color to blue .Border.Color = RGB(0, 255, 0) ' green .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2 End With En...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

OLAP query problem
Hi, I have installed Windows XP SP2 + Office 2000 SR1, when I want to create a OLAP database query, I can not find the dirver "Microsoft OLE DB Provider for olap service8.0", there is only a driver "Microsoft OLE DB Provider for olap service" (without 8.0). Where can I install the 8.0 driver? Thanks in advance Kevin I found the answer by myself. I hope it could help for others in the newsgroup: http://support.microsoft.com/default.aspx?scid=kb;en-us;303438 "Kevin Dai" <Kevin_Dzy@Hotmail.com> д����Ϣ����:%23ETmqihnFHA.420@TK2MSFTNGP09.phx.gbl... &...

How to add records to a table through a data access page?
I have set up tables and queries in a database, and am outputting them to a report, however what i want is to have users enter the data to the required fields through a Data Access Page. I have set up the DAP with all of the info, however when i include the ADD command, and try and use it, i am prompted with the error message that i cannot add because the recordset for the page is read only. I have tried the access help info to discable, however it says to find the read only property which i have not been able to locate. Any help that can be given will be greatly appreciated. Thanks ...

Create a temp database
Hi! We have an application which uses a SQL-server 2005 database in the backend. We would like to add a functionality where a user can take a snapshot of a database and then manipulates the data in that snapshot (so it cannot be reaonly which normal snapshots are) to see how the outcome will be if they add/change certain information in the system. When the user is finished the snapshot should be removed. We could always backup and restore a database but I want to check if there is a easier way to do this. We support SQL-server2005 today but if there exist functionality in the...

A Pop up message in Excel...
Just curious if this can be done... does anyone know a way to make a pop up message appear in Excel 2003 when an a file opens? Example, I have a particular file that is shared for others to update. When whoever opens the file I would like a little text box to say a few chosen words from me like "THIS FILE IS NOT TO BE MOVE OR RENAMED" or anything else I choose. Thanks in advance, Ian Hi Ian, In a standard module paste: '=============>> Public Sub Auto_Open() MsgBox "THIS FILE IS NOT TO BE MOVE OR RENAMED" End Sub '<<============= --- ...

Excel asking for save with no changes #3
Yes, "=NOW()". I'll try removing it. Thanks -- tommy2 ----------------------------------------------------------------------- tommy20's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=281 View this thread: http://www.excelforum.com/showthread.php?threadid=27781 ...

Place a form control value in Access to a specific cell in Excel.
I didn't get a response on my last post, so I'm hoping someone will have an idea on this request. Below is my original post: Hi, All! I'm looking for some help in moving data from my Access form to specific cells in an Excel template. I'd like to create a command button on my Access form that will open the .xlt file and transfer some of the values on my form and plug them into the .xlt. For example, I have the cost of equipment calculated on my form control (3 printers @ $300 each = $900), the command button will capture that value and plug it into the capital expenditure ...

FetchXml to SQL Query
I'm looking for a tool that converts FetchXml to equivelent SQL Query. Can you help? Not aware of anything that does this. If you are fairly handy with XML though it shouldn't be that difficult to write. -- Matt Parks MVP - Microsoft CRM "CRM Chief" <CRMChief@discussions.microsoft.com> wrote in message news:600AB470-BCA8-48F9-9CD8-652835945661@microsoft.com... I'm looking for a tool that converts FetchXml to equivelent SQL Query. Can you help? ...

Top row of excel stays even in sorting
I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

Help with query 04-20-10
Hello, I need some help with a query that I can't quite figure out the logic for. I need to select the primary key field from table1 where there are no records in which field1 are null and in which the number of records in which field1 =1 is greater than zero. I've tried several permutations to get this query to work and have not had success. Can someone give me a hand please? Thanks S I am not sure I understand the requirements, but see if the following example will help: CREATE TABLE table1 ( keycol INT NOT NULL PRIMARY KEY, datacol INT); INSERT INTO...

Trying to get user id in a query
I'm trying to get access to the security variables in an access 2003 session, in order to get the userid or user name and use it as a parameter in a query. Is there any way to do this? Regards, Javier Garc=EDa Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function UserNameWindows() As String Dim lngLen As Long Dim strBuffer As String Const dhcMaxUserName = 255 strBuffer = Space(dhcMaxUserName) lngLen = dhcMaxUserName If CBool(GetUserName(strBuffe...