Solved set value of a record field in a table based of the aggregate of another table (1 Viewer)

[HabibValil]

I have a table of Products and a table of Packages. Each product has a serial number. Each 4 products are packed in 1 package.
My product table has a Package field where I tell the database that each product goes to which package.
There is a Yes/No field in Packages table that must be set AUTOMATICALLY to Yes for the package if there are 4 products in the package.
I don't know how to change that Yes/no field to Yes when the package is full ( 4 products are in there).

how would you do that?

I am not very familiar with SQL. please tell me what to do in Design mode of query, if possible

 

[Gasman]

Use Dcount() ?
I would not even set it to Yes, just count, if =4 or >3 then package is full?

 

[plog]

There is a Yes/No field in Packages table that must be set AUTOMATICALLY to Yes for the package if there are 4 products in the package.

That's not how databases are to work. You don't save data you can calculate. Like if you had a [Date Of Birth] field, you wouldn't store [Age]. You would calculate [Age] when you need it based on the value in [Date Of Birth].

So, what you should do is build a query to determine what you need to know and reference that query when you need to know it. Can you demonstrate your issue with data? Provide us with all relevant tables and fields (include names) and then show us what you want to occur.

 

[HabibValil]

That's not how databases are to work. You don't save data you can calculate. Like if you had a [Date Of Birth] field, you wouldn't store [Age]. You would calculate [Age] when you need it based on the value in [Date Of Birth].

Thank you. I am starting to learn something important in databases and such advice is really helpful.

So, what you should do is build a query to determine what you need to know and reference that query when you need to know it. Can you demonstrate your issue with data? Provide us with all relevant tables and fields (include names) and then show us what you want to occur.

about my question, to complete the picture:
the packages I mentioned in my first post are themselves to be packed in larger packages. each large package contains 10 of small packages. In my file I refer to small packages by SP and I refer to large packages by LP.

I understand that for the first step ( packing products in small packages), I dont need to store the status of small packages whether they are full or not. But in the next step when small packages are to be packed in large ones, without a field to identify full small packages, I cant put them in large packages.
And I should remind that the each product has a serial number and I want to be able to track each one of them.

....
unfortunately I could not attach the file. it is 8 MB and is considered too large as an attachment. It has 3 small tables and 3 queries and 3 simple forms. I dont know why it is so large in size!

 

[CJ_London]

Try compacting the db, then zip before uploading

 

[HabibValil]

Here is my file.

 

[The_Doc_Man]

But in the next step when small packages are to be packed in large ones, without a field to identify full small packages, I cant put them in large packages.

See the earlier comment made about knowing how many smaller packages are in a particular large package. Let me demonstrate the concept in code. You were asking about putting a Y/N field to say that a package was full. But if a Large Package is always full when it has 4 small packages in it, then in code:

IF PackageFull = TRUE Then ...

is equal in result to

If DCount( "SP", "PackageLoad", "LP=" & LPID ) = 4 Then

That is, an IF statement provides a True/False answer whether you are testing a Y/N field or the count of some complex JOIN query. Then you merely have to figure out the parent/child relationship that will let you know how many small packages are in a given large package. You can look this up as parent/child or independent/dependent. Potato, potahto.

 

[HabibValil]

Thank you everyone for trying to help me. I spent hours on this with the aid of your comments and internet, but I couldn't go beyond this step of this work. I think the concept of database is not correctly established in my mind. But I am very interested to learn.

I could not realize where to place the Dcount; in Criteria field in the query? as a new column in the query? in SQL view? in VBA environment.
I will be grateful if someone can help me pass this stage where I am stuck.

I describe the problem again:
I have Products with serial numbers. each 4 products (provided that they pass quality control) should be packed in 1 SmallPack. And then each 2 SmallPack should be packed in 1 LargePack.
(So far I have created the SmallPack and LargePack tables manually, but I think they can be generated automatically in Access depending on quantity of Products. This will be my next step to do.)

I have removed all queries and forms in my file and attached it again here. Only tables of Products, SmallPack, Largepacks are there in my file.

 

[CJ_London]

Please describe in simple language what you are trying to do - i.e. how you are doing this manually at the moment

in particular you say

I have Products with serial numbers

1. the product table would appear to have no indicator of product. Unless you only produce one product? If you have multiple products then the solution you require would be different.

2. Serial numbers are usually unique - but you have duplicates (MAXK12343). We don't know if this is a typo or the true case (one is marked as quality control and the other is not). Again this can impact the solution.

3. Also, there is no field to indicate whether that serial number has already been packed or not - so how do you determine that? Is that the SPID field? If it is then when and how does the small package get created?

 

[arnelgp]

i did not use LargePack/SmallPack table.
instead i create a Single table, Packs.
i also added a junction table that will save the productid, smallpackID and LargePackID to FinalPacks table.
i did this using VBA.

see the result in Query1.
you noticed that ProductID 591, 592 and 594 are not included in the FinalPacks table
it's because you only provided up to 6 small packs (therefore, all small packs have all been used).

 

[HabibValil]

Please describe in simple language what you are trying to do - i.e. how you are doing this manually at the moment

By manully, I meant I add small packs records in the SmallPacks table, and I add large pack records in LargePacks table. The right way is that when each pack is full a new pack gets created automatically.

in particular you say

1. the product table would appear to have no indicator of product. Unless you only produce one product? If you have multiple products then the solution you require would be different.

Yes I have a single product with different serial numbers.

2. Serial numbers are usually unique - but you have duplicates (MAXK12343). We don't know if this is a typo or the true case (one is marked as quality control and the other is not). Again this can impact the solution.

Yes. My serial numbers are unique. That duplicate is a typo.

3. Also, there is no field to indicate whether that serial number has already been packed or not - so how do you determine that? Is that the SPID field? If it is then when and how does the small package get created?

Yes. SPID is the field where the product is assigned to a small pack. Also in SmallPacks table, LPID field is the field to indicate where a small pack goes.

 

[HabibValil]

i did not use LargePack/SmallPack table.
instead i create a Single table, Packs.
i also added a junction table that will save the productid, smallpackID and LargePackID to FinalPacks table.
i did this using VBA.

see the result in Query1.
you noticed that ProductID 591, 592 and 594 are not included in the FinalPacks table
it's because you only provided up to 6 small packs (therefore, all small packs have all been used).

Thank You. It is a different and I believe a better way to keep record of products and packs. let me go deep through your file and reply back.

 

[arnelgp]

The right way is that when each pack is full a new pack gets created automatically.

i can modify it again and include that feature.

 

[HabibValil]

i can modify it again and include that feature.

That would be great! I had used vba in excel but i didnt know how to manipulate databases in vba. Your code helped me to get started using vba in access. thanks a lot. I will try to change your code so that whenever packs are full a new pack gets created. I hope I can do it, otherwise I will come back.

one question here: wass it possible to do this packing project in Access without coding in vba or sql? ( I mean only by using the dashboard of Access)

 

[arnelgp]

i don't think you can do it without VBA.
one question, on my post #10, there are 3 products left.
meaning 2 can be placed in small pack and 1 left (no partner).
also you can't put it in Large pack since only 1 small pack is produced.

how do you deal with this?

 

[HabibValil]

In reality there are much more products to be introduced to the Products table. So the last (single) product waits for its partner to come and then your module will be run again and they both gets packed.

After I received your code I have been reading it over and over to learn what you have done. I realized how OpenRecordset is working. I realized you have created 3 queries in vb. I created a query in Access and transferred your code to the sql view of the query. Now I am visually seeing what you have done.
I attached a snapshot of your translated query in Access environment. is it correct?
And the Criteria field in query, I didnt know that can have such syntax. I will have to search for more possible syntax for that field. That Criteria is actually another query. So, the criteria of a query field can be another query. Am I right?


I also realized that if i want to add more records in Packs table I need to change the type of OpenRecoredset to Dynaset rather than Snapshot that you have used. Am I right?

your code was a great start point for me. I am so grateful. I wouldnt learn these if I have watched many hours of youtube videos.

 

[HabibValil]

In fact you have created 6 select queries in total, plus 3 update queries?