How do I link an excel spreadsheet or spreadsheet like data to a f

[Stuart Reicher]

I am creating a new database to track project data. Part of each project is
budgetary data. There are approximately 20 types of data with amounts in
table like format. I could put them in the table as fields but, have no easy
way to get the data to excel later on to manipulate. So I was thinking of
either embedding or linking the spreadsheet to each record. When we are done
we want to run a query that creates a list of jobs say in a city. Then be
able to bring up the budgetary data to review in excel format. That data can
either be in excel or exported to excel or copy and pasted into excel.

 

[Klatuu]

It isn't clear what you are asking, but here are some points to consider.
First, you cannot modify data in a linked spreadsheet. You can only read
from it. I would suggest you either link to or import the spreadsheet data
and use the TransferSpreadsheet method to export the data to excel.

 

[Stuart Reicher]

Here's what the user cares about:

1. Tracking his projects by entering them in a database
2. Be able to somehow link the individual budgets/costs to those projects.
3. Create a query so in the future we can look for similar projects and
their costs so we can see if the costs are in line. This would be done by
copying the old cost data into a spreadsheet template that already has
formulas to manipulate the data.
The costs would look like this:

Electrical 100000
Plumbing 200000
elevator 55000

There are approximately 20 of these per project. So, I know how to add
these to a table however when they are exported to excel from a query they
won't show up this way. That's why I asked the question. I tried switching
the rows to columns in excel but that doesn't work very well. I need to
either get this data from access to excel in the proper format as shown above
or put the data in it's own spreadsheet for each record and link it in the
entry form. Unless you have any other ideas.

 

[Klatuu]

Try using a crosstab query to export the data in the format you need.

 

[Stuart Reicher]

I tried it, it only allows one value row. Is there a way to link or
embed a spreadsheet into each form/record

I need the data to look like this

Project 1234 5678 9112


Electrical 100000 10005 1006
Plumbing 200000 200005 200006
elevator 55000 550005 55045

 

[Klatuu]

You don't link a spreadsheet to a form or a record directly.
You must first establish the spreadsheet as a linked table. You cannot,
however, change data in a linked excel table.

 

[Stuart Reicher]

If you are talking about just linking a spreadsheet to the database that's
kind of useless because every project/record would have its own spreadsheet.

 

[Klatuu]

You could use the Form's current event to link to a spreadsheet for each
record.