Showing EXCEL data in ACCESS reports

[utzja1]

I'm relatively new to Access, so I'm wondering if my goal is impossible or just solved in a different method. All suggestions welcomed.

I have a database that includes an attachment field which is currently populated with Excel workbooks. These are not massive workbooks and contain only two worksheets for each record. The first worksheet allows staff to enter quantities and unit costs for maintenance items (no more than 20 items, which without going into specifics, is more items than will ever be needed). The other worksheet sorts the data by total cost and reports the estimated maintenance total and the top three maintenance line items.

I'd like to create a report in Access for each record that shows, among other things, the top three maintenance items and maintenance estimate for each asset (the range I'd be looking at would only be 5rX5c). I'd do this within Access BUT the number of items for each record could conceivably vary, hence the Excel file. Is there a way to show this data on the report?

Is the following an option? Limiting the number of maintenance items and unit costs to a fixed number (say 6, for argument's sake), then creating a query with calculated field which figures the estimated maintenance cost for each, then filtering the calculated fields to report the Top 3 items. This way I could query all this data and use it to populate the fields on the report. It seems plausible but I'm left wondering if there is a more clever way to skin this cat.

Thanks, any consideration is appreciated.

 

[pbaldy]

This isn't really an answer to your question, but why the Excel files? You imply that it's because of the variable number of items per record, but typically that would be handled with a separate table, related one-to-many with your existing table (one record per item). It sounds like you're storing data in Excel that should be in Access, and running into the inevitable problem of joining them.

 

[utzja1]

Thanks! The linked table is an option I hadn't considered yet. I have much more experience with Excel and tend to approach Access problems with Excel solutions. I will definitely try this.

 

[pbaldy]

No problem! I think you'll be happier in the long run.

 

[utzja1]

Question for clarity -

You referenced a one-to-many relationship between the tables. How would link the two tables if the maintenance cost table has no other fields in common with the main table, aside from the field that holds the Asset ID? I tried using the Edit Relationships menu, and when I entered the Left Table and Right Table information, it automatically flagged a one-to-one relationship. I understand the concept but I'm hung in the implementation.

 

[BigHappyDaddy]

Can you post your table structures and some non-senstive example data? It would help other forum members to point you in the right direction.

 

[pbaldy]

The asset ID should be enough. It's not a key field in the maintenance cost table, is it?

 

[utzja1]

I initially chose not to have the Asset ID as a field in the Maintenance Cost table since I'd have to enter the Asset ID with each record (fields would be Asset ID, Maintenance Item, Quantity, Unit, and Unit Cost), and every book I've picked up on Access tells me to avoid duplicate data at all costs. I wound up creating a separate table for Maintenance Cost with one record per AssetID and enough fields to handle 8 maintenance items, which should be more than enough. The client may think it's a little rigid, but I could always up the number of fields if I had to, I guess. So that's why when I linked the two tables (based on Asset ID), Access determined it was a one-to-one relationship.

Would you be suggesting having the primary key as an autonumber field, then adding a field for the Asset ID? I could add records as I wished at that point, and then link the tables on the Asset ID. The query used to create a report could then just search on the Asset ID. is that what you're suggesting?

 

[pbaldy]

What you've done is not normalized, and I wouldn't do it. Storing the ID field isn't storing duplicate data, it's storing the key value that relates one table to the other. If you also stored the asset description in the maintenance table, that would be storing duplicate data.

Would you be suggesting having the primary key as an autonumber field, then adding a field for the Asset ID? I could add records as I wished at that point, and then link the tables on the Asset ID. The query used to create a report could then just search on the Asset ID. is that what you're suggesting?

Yes, that's what I'm suggesting (that's the normalized structure). Then an asset could have any number of maintenance items.

 

[utzja1]

Thanks, it might be a rush to judgment on my part but I think I understand that.

 

[pbaldy]

No problem, post back if you get stuck.