Re: [MS_AccessPros] Making single entry for bulk consignment same Product Name having same weights of Cartons

 

Hi John,

My apologies i could not reply you earlier as my health was not good and i was away from my laptop/net.

In the meanwhile while i was at home i made a solution for my requirements and setup. In my case we have only Text box to enter "WeightOfCarton" and that entry is assumed as Weight in "KGS".

I included a Text box "CartonQty" having Default  Value 1, Tab Stop --> No, so when my user is doing data entry he enters "CartonNo" and obviously "CartonQty" is set to 1, but when it is the same "Product" cartons quantity is more than one he enters that quantity "CartonQty".

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

If your report groups on product name, you should get both the "each" and "case" versions grouped together.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Mar 13, 2017, at 13:58, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Now i am getting the logic you are telling. But still i have some reservations.

For e.g in some reports one product is shown in a single entry. The way you say means that  a product name "X" is same for "each" and "case". I assume we would not be able to show as single entry in report, this will show two rows for product "X".

Am i thinking right ? and if only this is the solution i have to get approval from my user.

Is there any other possibility or way to manage it with my existing products as they are now ?

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I'm suggesting that the *product* be different.  Two products might be the same, but one indicates an "each" product (shipped in individual cartons) and the other indicates a  "case" packaging of the same product - multiple items in one carton.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Mar 13, 2017, at 11:11, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I have reviewed your suggestion again and again and i get that you are suggesting me the type of Product, not the number of cartons. In my case on the form for any Product its "CartonNo" is unique for same consignment and client, no matter how much is "ProductQty" and how much is "WeightOfCarton". This means for each row Carton is ONE and what so ever numeric marking of carton is.

Yes it is Continuous form.

My user's requirement is that suppose he enters a "Lot" for some product containing many cartons, he will manually calculate "ProductQty" and "WeightOfCarton" of this "Lot" and enter it. There is no control on the form at present for "Lot" for this i have thought to enter it under control "CartonNo". On my present form design and coding this all can be accomplished.

But only one thing is teasing me that how many cartons were in this "Lot"?

For example in a report "Cargo Collection" its Record Source is "CollectionVoucher Query"

SELECT qryGroupCartonsCollectionVoucher.ConsignmentNo, qryGroupCartonsCollectionVoucher.ClientCIN, Clients.ClientName, Clients.CosigneeName, qryGroupCartonsCollectionVoucher.Destination, Count(qryGroupCartonsCollectionVoucher.CartonNo) AS TotalCartons, Sum(qryGroupCartonsCollectionVoucher.Weight) AS TotalWeight, Sum(qryGroupCartonsCollectionVoucher.TotAmount) AS TotalAmount, [Consignment Number].ExportDocs, [Consignment Number].FormENo, [Consignment Number].FormEDate, [Consignment Number].FormEMadeBy

FROM (qryGroupCartonsCollectionVoucher INNER JOIN Clients ON qryGroupCartonsCollectionVoucher.ClientCIN = Clients.ClientCIN) INNER JOIN [Consignment Number] ON qryGroupCartonsCollectionVoucher.ConsignmentNo = [Consignment Number].ConsignmentNo

GROUP BY qryGroupCartonsCollectionVoucher.ConsignmentNo, qryGroupCartonsCollectionVoucher.ClientCIN, Clients.ClientName, Clients.CosigneeName, qryGroupCartonsCollectionVoucher.Destination, [Consignment Number].ExportDocs, [Consignment Number].FormENo, [Consignment Number].FormEDate, [Consignment Number].FormEMadeBy;

SQL of "qryGroupCartonsCollectionVoucher" is:

SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, Sum(CollectionVoucher.WeightOfCarton) AS Weight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix, Sum(CollectionVoucher.Amount) AS TotAmount

FROM CollectionVoucher

WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Collection Voucher Report]![cmbConsignmentNo]))

GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

You can see that in the above query we are counting CartonNo as TotalCartons

==>Count(qryGroupCartonsCollectionVoucher.CartonNo) AS TotalCartons,

Hope you got my issue, could you please re consider it and if you can reach to some solution, and i am rest assure you can, as always you have been.

Regards,

Khalid

 

 

 

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

John,

Thanks for the suggestion. I will create it and start work.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Creating a "development" database sounds like a good idea!

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 11, 2017, at 3:31 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thanks for giving me some way to handle the situation, but still there are many questions in my mind ahead.

First of all, what i assume is that this task for entering bulk entry would definitely bother me in this form's design and code at different events.

This is a request from my user not a compulsion, and i have told him i will try it.

So the scenario in my mind is that i don't mess up with my current database, instead i make a new development database importing every thing from my current database. So that we can freely make changes and experiments on the development database, if we achieve our goals and get all reports accordingly we may apply them to our current database.

Need you suggestion and guidance.

Regards,

Khalid 

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I've tackled similar problems to this in my inventory applications.  For example, I might sell a product by the "each" but buy the product by the "case".  One way to deal with this is to have a product listed more than once in your database.  The first version indicates individual products, and the second is a "case" that contains a multiple of the single product.  You can also have a table refer to itself so that the "case" entry lists the "each" entry as the related product contained inside the case.

As for totaling on a form, if you're using a continuous form, you can add a control in the form footer that sums any numeric column.  The control source looks like =Sum([Field Name]).

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 11, 2017, at 9:53 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

My user has a new demand for data entry on the form "New Cargo Collection Input" having subform "NewCargoCollectionInputsubform".

The situation is that the goods received from different clients have different Products in different Cartons, some times the Product is same, but packed in different cartons having different quantity and different weights. So there entry is made separately for each carton.

On the form there is no such control to mention that there are total how many cartons for this particular "ConsignmentNo" and Client. In different reports we get total cartons from different queries and checks, most of these are with consultation with you and your worthy suggestions and help.

Now the demand is that some times some clients do deliver some products in bulk packed in many cartons having normally same weight of each carton. Suppose a product "A" has 300 cartons or bags with same product and same weight of cartons or bags, my user needs instead of entering 300 cartons separately one by one, he may be able to make a single entry for these 300 cartons.

Now i am not getting the solution how to manage it.

Record Source of subform is:

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductID, Products.ProductNameEnglish, Products.ProductNameRussian, Products.HSCode, CollectionVoucher.BrandName, CollectionVoucher.ProductQty, CollectionVoucher.UnitOfQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.UnitOfGrossWeight, CollectionVoucher.[Net Weight], CollectionVoucher.UnitOfNetWeight, CollectionVoucher.UnitOfValue, CollectionVoucher.Rate, CollectionVoucher.Amount, CollectionVoucher.ID

FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductID = CollectionVoucher.ProductID

ORDER BY CollectionVoucher.CartonNo, CollectionVoucher.ProductID;

Help required please.

Regards,

Khalid

__._,_.___

---

Posted by: khalidtanweerburrah@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (13)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___