Solved Importing and calculating the data

[Robrobby]

Hi all

I'm currently planning to convert an excel maintenance sheet which I use to record servicing and repairs for each of our moulding tools.

One of the things I used to do monthly in excel was to import a monthly exported report from our planning system which contained all the orders that our machines had produced for that month and calculate if they needed a service or not.

On my "ToolDetails" sheet I would have a list of each unique tool which had info like the following, which was used to keep a count of the production hours each tool had run since the last import.

ToolNo 123
PartsMadePerHour 100
ServiceMaxhours 300
CurrentProductionHours 290

We could have 1000's of orders completed within the month and when exported into a spreadsheet it would have every order on a seperate line with the part quantities created for each item.

In excel I used VBA to open up the exported jobs data and paste it into a temp sheet. on my ToolDetails sheet i would use a SumIf formula in one column to take all the values off the imported sheet, another formula to add them together and check if the quantity exceeded the max limit. If it did then it would flag it as yes in that cell ready for it to be processed. Vba would then filter and copy those details to the main sheet. A third column with the combined quanties would then be pasted as text into the Current hours column (unless it was flagged as yes where it would put a zero instead) The imported sheet would then be cleaned up ready for next month.

I would like to do the same in access but my knowledge of processing data and calculating the data like this in access is basic.
The only thing I can think of is to import into an empty table, loop through each entry in the imported data, query every part code, query each tool number and add the time to the tool details table record. Then another loop to check each tool and see if the hours are greater then the maximun hours, then combine/zero depending on the values.

But with thousands of monthly sales in the monthly export every month this sounds very inefficiant to me as there are many different tools which produce many different parts and would have to go through every single record one by one and check/process every single line. Then another loop to go through every tool to see if the imported hours had exceeded the maxiumum hours. Thousands of queries where I could have done it with 3-4 formula columns and a basic copy/paste macro in excel.

Is there an easier more efficient way of doing this with the imported data and the existing tool table in access?

 

[ebs17]

I would like to do the same in access

Do you want to recreate Excel methods in Access?
A database of any kind works significantly differently. If you don't realize this, you're wasting time and effort.

 

[Robrobby]

Forgive me if this repeats, my first reply didn't appear.

I want to move away from excel, I need a way of importing into access and and calculating the imported data against the existing records and then using those results to create entries in another table if matching a preset criteria.
I'm thinking up ideas as I type, but it's the adding the quanties up from a query which confuses me.

Maybe a query to select matching data and then add the combined results into one field on another table.

 

[ebs17]

The first step should always be the creation of a database schema (structure of tables and relationships), whereby this schema is based on objective requirements and does not have to have anything to do with existing Excel tables.

Once these functional structures have been created, one can think about how content is transferred from the Excel tables to the tables of the database schema.

 

[GPGeorge]

Forgive me if this repeats, my first reply didn't appear.

I want to move away from excel, I need a way of importing into access and and calculating the imported data against the existing records and then using those results to create entries in another table if matching a preset criteria.
I'm thinking up ideas as I type, but it's the adding the quanties up from a query which confuses me.

Maybe a query to select matching data and then add the combined results into one field on another table.

At the risk of flogging a dying horse, I want to support what Eberhard is saying.

I have used the term "Application Bias" to describe an all-too-common tendency to assume that knowing how to use one application is a benefit in learning others. E.g. Excel and Access. If you know Excel, you only need to adapt your existing data for Excel and continue to work with it as before. That is, unfortunately, not true at all.

The first step in to design the Access relational tables appropriately, as noted. If you do not do that, problems will never end.

 

[Robrobby]

My schema for the basic database is fine, it's just the importing and calculating as one stage that I am trying to work out.

Think I've figured it out:

SELECT SUM(TotalHours) AS Importedhours
FROM ImportedData;

then take the current value from my existing table, run my code based on the combined result and update my records.

Simple when I look back at it now.
I know what I'll be doing tomorrow.,

 

[Robrobby]

ChatGPT figured it out for me.


-- Query the existing values for the specified ToolNo
SELECT TotalHours, MaximumHours
FROM Tools
WHERE ToolNo = 'your_tool_number';

-- Update the TotalHours field by adding the ImportedHours value
UPDATE Tools
SET TotalHours = TotalHours + [imported_hours_value]
WHERE ToolNo = 'your_tool_number';

-- Check if the updated TotalHours value exceeds the MaximumHours
SELECT CASE WHEN TotalHours > MaximumHours THEN 'Exceeded' ELSE 'Not Exceeded' END AS Status
FROM Tools
WHERE ToolNo = 'your_tool_number';

 

[pbaldy]

Forgive me if this repeats, my first reply didn't appear.

FYI you clicked on Report instead of Reply with your first reply. No worries.

 

[Robrobby]

FYI you clicked on Report instead of Reply with your first reply. No worries.

Sorry, I didn't realise. Wondered where it went.

 

[pbaldy]

Sorry, I didn't realise. Wondered where it went.

No worries, you're not the first.

 

[GPGeorge]

Perhaps this is a good discussion to drop in a link to my blog post on the problem with using ChatGPT.

 

[Pat Hartman]

ChatCPT isn't giving you the best answer. Do not ever rely on it although it is pointing you in the right direction.

No updating of any kind is necessary. All you need is one query and a report bound to that query. You don't even need to import the data if you have no historical need to keep it. You just link to the spreadsheet, assuming it is formatted as a table. If the spreadsheet is formatted as a report, you need to futz with it to fix it or get the other team to change the export to a format you can use without modification.

The report does the summary. If the Inspection point is the same for all tools, you can hard-code it in the query or for convenience use a form to run the report and add an unbound control with the default value so you can change it more easily.

If the inspection point is different for different machines or for different types of machines, then you need a table with a list of machines/types that holds the appropriate number. Then your query uses two tables, the work flow for last month joined to the machines/types table so you can get the inspection point for each machine.

This sounds like an application where you want to start fresh each month so we can help you with that. Other options are available should you need to support historical reporting.

 

[Robrobby]

ChatCPT isn't giving you the best answer. Do not ever rely on it although it is pointing you in the right direction.

No updating of any kind is necessary. All you need is one query and a report bound to that query. You don't even need to import the data if you have no historical need to keep it. You just link to the spreadsheet, assuming it is formatted as a table. If the spreadsheet is formatted as a report, you need to futz with it to fix it or get the other team to change the export to a format you can use without modification.

The report does the summary. If the Inspection point is the same for all tools, you can hard-code it in the query or for convenience use a form to run the report and add an unbound control with the default value so you can change it more easily.

If the inspection point is different for different machines or for different types of machines, then you need a table with a list of machines/types that holds the appropriate number. Then your query uses two tables, the work flow for last month joined to the machines/types table so you can get the inspection point for each machine.

This sounds like an application where you want to start fresh each month so we can help you with that. Other options are available should you need to support historical reporting.

Thanks for the info. As soon as i reach this part of my project I’ll come back with all the relevant files.