a simple query question

[SahandSabahi]

During the Production, below information are filled in paper forms for each machine in the workshop and then the information in the form is later entered in corresponding access tables and fields:



1- the Batch number (somehow identifies the product)
2- Form Number
3- the date of production
4- Machine
5- time intervals of the machine working and the number of employees attending to the machine . for example 2 persons from 10 AM to 12 PM and 1 person from 13 PM to 15 PM.
7- the quantity and length of the production in total time intervals registered in this form. for example 10 pieces of 3 meters each, 25 pieces of 3.5 meters each and 15 pieces of 2.8 meters each. (these are all defined as the same product although the lengths are different.
8-the wasted product in the total time intervals registered in this form. for example 12 pieces of 0.15 meters each.

please note that the production procedure doesn't allow us to count the number of products and the length of them for each interval separately. therefore, the quantity of the products and the lengths are determined for the total time intervals and do not correspond to each separate time interval.

I need a query with below output:

	Total person hour	total length of the production	total length of waste
Form A	the total sum of time intervals multiplied by the number of persons for form A	the total sum of the pieces produced multiplied by their length for form A	the total sum of the pieces wasted multiplied by their length for form A
Form B	...	...	...

I am attaching the data base here as well.
Timing V06.1 97-10-12 1733-1.accdb.zip

I can create three separate queries for each column (TotalLength, TotalWaste, TotalPersonHour queries) and then aggregate the results in a fourth query (Aggregate) to get the above result. I am curious to know if this can be reached directly in one query.

thank you

[isladogs]

Quite possibly but you need to supply field names in order to give a definitive answer
In fact give the SQL for your three queries

BUT it sounds like Workshop1 and Workshop2 have the same structure.
If so, these should be combined into a single table with an extra field WorkshopNumber.

You would then definitely be able to do this as a single aggregate query. Group by workshop and project and sum the total hours

[SahandSabahi]

thank you ridders52,

I have actually used an analogy to simplify my database and explain the question easily. the concepts being measured in the two workshops are different and the table structures of workshop 1 and 2 are not exactly the same. please assume the same structure (Design) and relationships I have explained and see if there is a solution without changing the design.
for the fields,
assume Projects table with below fields:
ID, ProjectName,

assume workshop1 and workshop2 tables with below fields:
ID, Project, Date, Start time, End time,

[orange]

I recommend you post a copy of your database in zip format - often better than trying to work with an analogy.
Date is a reserved word in Access. You will encounter syntax errors at some point if you use a naming convention that allows embedded spaces in field names.
Good luck with your project.

[isladogs]

Yes I agree. Analogies don't necessarily help and can cause confusion.
Even if some fields are different, there is enough community for at least the time part to be merged.

If you post a stripped down database with those 3 tables, your queries and some sample data, someone will see if a simpler solution is possible.

[SahandSabahi]

Ok, done.
I have attached the original database and explained the question directly.
I am very new to asking questions on forums. thank you for your advice.

[isladogs]

Ok the new problem is the database has 8 tables and no queries.
Some of the fields /records are in a language that I can't read.
As you have edited the original post, I can't compare what you wrote before with the new explanation

You refer to a form but there are no forms.
Confusingly there is a table called form!

So I have no idea what to look at and am unable to do anything with this.
Suggest you upload another version which only contains the tables needed together with your original queries that you wanted to streamline with the required results.
Then explain exactly what we should look at.

[SahandSabahi]

Suggest you upload another version which only contains the tables needed together with your original queries that you wanted to streamline with the required results.
Then explain exactly what we should look at.

thank you for following up ridders52.

I revised and re-attached the database:

I have translated the fields (not the records). sorry for that.
I have included the three queries that lead to the required result (aggregate query) and want to know if the aggregate query can be reached directly.
I have included the main "Form" and it's three sub-forms used for data entry.

Confusingly there is a table called form!

yes, I have unprofessionally named one of my tables as "Form" since it corresponds to the actual paper-form used in the workshop by the workers.

hope I am clear now.

[isladogs]

Hi
I've had a quick look at this.
You can easily create one query by just joining the 4 tables and using the same expressions.
However, the combined version will give incorrect results due to the different number of records for each station in each table.
Fixing that will take more time than I can spare today

Two things:
1. By using inner joins, you are only getting results where all tables contain the same station value. Fine if that's what you want.
2. Suggest you use the Round function to 2 decimal places for each expression e.g. Total Length: Round(Sum([Length]![BarNumber]*[Length]![BarLenght]),2)

Sorry I wasn't that much help to you after all
Perhaps someone else will look at it & provide a better answer

[SahandSabahi]

Hi
However, the combined version will give incorrect results due to the different number of records for each station in each table.
r

Yes, that exactly summarizes my question. how to reach the correct results in one query.

Hi
Two things:
1. By using inner joins, you are only getting results where all tables contain the same station value. Fine if that's what you want.
2. Suggest you use the Round function to 2 decimal places for each expression e.g. Total Length: Round(Sum([Length]![BarNumber]*[Length]![BarLenght]),2)

thanks for the notes. Especially the first one helped my understanding of how this works.