ACCESS FEED: Re: [MS_AccessPros] How to enter different product in the same carton

Jumat, 31 Oktober 2014

Re: [MS_AccessPros] How to enter different product in the same carton

John,

Perfectly right!

Thanks a lot John once again.

Khalid

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

Khalid-

After I sent my last response, I thought that might be the case. Add a filter on ClientCIN like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN])

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 31, 2014, at 2:53 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The code you gave me it counts total cartons of a particular Consignment, but i need TotalCartons for specific Consignment and specific selected ClientCIN.

I assume that ClientCIN should be in this code, i've tried myself but getting error.
ConsignmentNo is Text
ClientCIN is Numeric

Khalid

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

Khalid-

Ah! The clue is you have now told met that this in the ConsignmentNo footer. Change the Control Source to this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = " & [ConsignmentNo])

I don't remember if ConsignmentNo is text or not. If it is text, you'll have to add quotes like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "'")

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 31, 2014, at 1:13 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

OK, i will follow this method.

But what about getting CartonTotal on the report's ConsignmentNo Footer, which is not coming correct?

Khalid

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

Khalid-

For cases 1 and 3, use a letter suffix. For case 2, use a number. When a carton that is in case 1 or 3 has multiple products in one carton, use a letter AND a number. The default value for suffix should be blank or 0. If you use 0, start with 1 to indicate multiple products in a carton.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 31, 2014, at 10:16 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes! of course a good catch from Robert (Thanks Robert). It worked smoothly and now i can see ClientName on cmbClientCIN along with its relative ClientCIN.

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY Clients.ClientCIN"
End Sub

Regarding your question for the Total Cartons:
You wrote:
If you run "qryGroupCartons" (you say you have now named it "qryGroupCartonsFreightInvoice") by itself, do you see one row per logical carton? It should be lumping together all the cartons that have a numeric suffix indicating multiple products in one carton.

Yes i can see one row per logical carton.

On Reports ConsignmentNo Footer Text Box TotalCartons Control Source is:
=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

qryGroupCartonsFrieghtInvoice:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Sum(CollectionVoucher.WeightOfCarton) AS CartonWeight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Now i'm not getting correct TotalCartons

One thing which is still confusing me is how to enter CartonSuffix in Form CollectionVoucher.
Let me brief you the scenario of duplicate CartonNo

1- Same ConsignmentNo, same ClientCIN but having different DeliveryVr. Some CartonNo may be duplicate some may not.

2- Same ConsignmentNo, same ClientCIN but having multiple Products in certain Cartons.

3- Some Cartons are shifted from another Consignment some CartonNo may be duplicate, some may not be duplicate.

Khalid

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

Good catch, Robert!

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 30, 2014, at 6:23 PM, 'Robert Peterson' bob@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Should the last ClientCIN in the order by clause also have which table it is looking at.
Says ORDER BY ClientCIN
Should it be ORDER BY Clients.ClientCIN"

Bob Peterson
Alternate Finishing, Inc.
P: 978-567-9205 ext. 22
F: 978-567-8742
M: 978-333-0060
Our full range of services can be found at http://www.alternatefinishing.com/services.html .

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 30, 2014 5:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

Sorry about that - forgot that ClientCIN is in both tables. Do this:

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = Col lectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub