Intro help with linking Access to Excel 1

[Lotruth]

It is too much to ask to start from scratch with my questions here on the forum, so I am asking for a good intro reference or tutorial that someone may have run into online.

I do have one question though. I am able to link one cell at a time using Data/Import, however, I was wondering if there was a way to do this faster. For example, I have a database that has several records with itemid and amount fields. I need to link the amounts into excel using the itemid as a key. There are many of these, so I dont want to go through all the clicking each time. I was hoping that after doing it once, there would be a formula in the fx field that I could copy and edit, but there is not. How can I link these cells faster?

This may all be explained in some online reference. If so, please just point me to it. I have been looking, but I haven't been able to come across anything helpful so far.

Thank you.

 

[SkipVought]

Hi,

I would not LINK them. What would be the reason for a link?

I'd QUERY the Access table from Excel using Data/Get External Data/New Database Query -- MS Access Database...

The query can be refreshed each time Excel is opened, if you like.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

Thanks,

That is what I meant my link. I query the table by going through all the clicks from data/import external data/new database query/selecting database type/selecting database/selecting table/selecting fields/etc...

I am trying to avoid doing all of this for each cell.

 

[SkipVought]

For each CELL???

You could COPY the data range and paste that into a different sheet.

What are you trying to do?



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

I am trying to populate an excel file with budget data that is stored in access. The structure is different, otherwise I would just put the would database table there.

I am not sure what you mean by the data range.

 

[SkipVought]

Select IN the querytable

Data/Data Range Properties

What is the NAME?

That's a range name. Select/copy that range, you copy the query.

"The structure is different"????

Can you explain?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

In access, we have a lot of data separated into tables. We have to put this data into preformatted excel workbooks that dont have the same layout as the access tables.

 

[SkipVought]

Data in tables is RARELY in the format that the need warrants.

Yer being kinda STINGY with the info -- its like pulling teeth!

I can do many DIFFERENT or SIMILAR queries in ONE querytable, by manipulating the SQL.

So tell me what you are trying to do.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

It would be nice if I could send you the files, but I think you understand the sensitivity of financial data. Beyond that, I dont know how to present the issue in a way that you can understand.

I think I will go in a different direction. Thank you for the time you invested in trying to help.

 

[SkipVought]

We can work with structure and not actual data.

Or simple scrubbed examples.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[lespaul]

so, show some examples of the table and fake the data. Show us what your expected results need to "look" like and we can help. You really just need a query to structure them correctly.
[tt]
tbl1
Field1 Field2 Field3
data data data
data data data
data data data

tbl2
Field1 Field2
data data
data data


tbl3
Field2 Field2
data data
data data
data data
data data
[/tt]


Expected Results:
[tt]
Tbl1.Field2 tbl2.Field1 tbl1.Field3 tbl3.field1
data data data data
data data data data
data data data data
[/tt]

with a "picture" of what you have and what you need, very easy to help you.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Lotruth]

Ok, here's the excel file

A B
1 Personnel Salary
2 John Doe $B2
3 Jane Doe $B3
4 Total $B2+B3

Here is the database

ItemID Amount
11 $20,000
22 $30,000

What I need to do is put the amount of itemID 11 ($20,000) into cell B2. Currently, I do this by navigating to data/import external data/new database query in cell. I then select the MS Access database type, select the fiscal database, select the budget table, select the itemid & amount field, filter by the criteria itemid = 11, then delete the itemid field from the Microsoft Query window so that it wont show up when I submit the amount back to the cell.

This process puts the $20K in the B2 field as needed. My problem is I need to do this for B3 as well as B4-Bx and this is just one item on one worksheet. I have about 12 worksheets with maybe 30 items each and I dont want to have to do this process for each of those 360 cells.

 

[SkipVought]

So LOGICALLY, what is it that relates ItemID, 11 in the database to John Doe?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

The budgets table is a collection of IDs that link lineID, CostcenterID, and TransactionID with a primary key field of ItemID.

In the lines table, the primary key called LineID is 26 and the description of that line item is John Doe.

In the budgets table, the primary key called itemID is 11 and it links the lineID 26 to the amount $20,000 along with the other primary keys (IDs) from the various other tables like Costcenter and Transactions.

 

[SkipVought]

Select Lines.Description As 'Personnel', Budgets.amount As 'Salary'
From Budgets, Lines
Where Budgets.LineID=Lines.LineID

sumthin like this?


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

No, like this

Line Table

LineID Description
26 John Doe


Budget Table

ItemID LineID Amount CostcenterID TransactionID
11 26 $20,000 27 28

Personnel and Salary are just the headings of the columns in excel file. They correspond to description & amount in the database.

 

[SkipVought]

That's EXACTLY what I stated!

Select Lines.Description [b]As 'Personnel'[/b], Budgets.amount [b]As 'Salary'[/b]

You can replace the formulas in that sheet with a query as I constructed and it will be headed, Personnel and Salary and return the data WITHOUT FORMULAS!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Lotruth]

Thanks. It's 5:00. I'm going to my other job now.