Solved Importing and calculating the data (1 Viewer)

Robrobby

New member
Local time
Today, 07:56
Joined
Apr 4, 2013
Messages
15
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

Well-known member
Local time
Today, 08:56
Joined
Feb 7, 2020
Messages
2,002
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

New member
Local time
Today, 07:56
Joined
Apr 4, 2013
Messages
15
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

Well-known member
Local time
Today, 08:56
Joined
Feb 7, 2020
Messages
2,002
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

George Hepworth
Local time
Yesterday, 23:56
Joined
Nov 25, 2004
Messages
2,042
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

New member
Local time
Today, 07:56
Joined
Apr 4, 2013
Messages
15
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

New member
Local time
Today, 07:56
Joined
Apr 4, 2013
Messages
15
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';
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:56
Joined
Feb 19, 2002
Messages
43,587
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

New member
Local time
Today, 07:56
Joined
Apr 4, 2013
Messages
15
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.
 

Users who are viewing this thread

Top Bottom