Some Explanation with Access Query

[firoz.raj]

can anybody tell why this Ms Access query is not working .Kindly let me know the idea.Any help would be highly appreciated.

SELECT AccessRole.emp_ID, AccessRole.FormID
FROM AccessRole
WHERE AccessRole.emp_ID='281' and AccessRole.Form_id='4';

 

[namliam]

ID fields are usually numbers, not texts....
try:
WHERE AccessRole.emp_ID=281 and AccessRole.Form_id=4;

 

[firoz.raj]

I Simple want if ReadForm is selected.means if bit field is checked .then i want to see the corresponding value.but Nothing Comes .when i runs the query.Kindly let me know the idea.Any help would be highly appreciated.
Readform is a Yes/No field.

SELECT AccessRole.emp_ID, AccessRole.FormID, AccessRole.ReadForm
FROM AccessRole
WHERE ((AccessRole.Readform=1));

 

[John Big Booty]

Check boxes return a value of "True" or -1 when checked and a value of "False" or 0 when unchecked

 

[John Big Booty]

So your query could like like;

SELECT AccessRole.emp_ID, AccessRole.FormID, AccessRole.ReadForm
FROM AccessRole
WHERE ((AccessRole.Readform=-1));

or

SELECT AccessRole.emp_ID, AccessRole.FormID, AccessRole.ReadForm
FROM AccessRole
WHERE ((AccessRole.Readform="True"));

 

[namliam]

WHERE ((AccessRole.Readform="True"));

True shouldnt be a text string here John

WHERE ((AccessRole.Readform=True));

That is assuming ReadForm is a true checkbox

 

[John Big Booty]

Sorry my bad

Thanks namliam

 

[firoz.raj]

Can you tell me ?Why this Sql Query is not working.When i tried to save .it
is Saying Extra) in Query .Kindly let me know the idea.Any help would be highly appreciated.Kindly find the attachment also.

SELECT PurchaseOrder.PO_ID, PurchaseOrder.RefNo, Suppliers.sup_name, Materials.Productname, Materials.unit, PurchaseOrderDetail.item_code, PurchaseOrderDetail.item_type, PurchaseOrderDetail.Quantity, PurchaseOrderDetail.UnitPrice, Materials.openingBalance FROM (PurchaseOrder INNER JOIN Suppliers ON PurchaseOrder.sup_ID = Suppliers.sup_id) INNER JOIN (Materials INNER JOIN PurchaseOrderDetail ON (Materials.item_type = PurchaseOrderDetail.item_type) AND (Materials.item_code = PurchaseOrderDetail.item_code)) ON (PurchaseOrder.RefNo = PurchaseOrderDetail.RefNo) AND (PurchaseOrder.PO_ID = PurchaseOrderDetail.PO_ID) WHERE (((PurchaseOrder.PO_ID)=) AND ((PurchaseOrder.RefNo)=));

 

[John Big Booty]

It would appear that you have an extra right parenthesis. Print out your SQL and then match up each set of parenthesis, starting from the inside of each set, and working your way out until you have found the mismatch.

 

[firoz.raj]

Still issue .Kindly let me know .why it is not working.

"SELECT PurchaseOrder.PO_ID, PurchaseOrder.RefNo, Suppliers.sup_name, Materials.Productname, Materials.unit, PurchaseOrderDetail.item_code, " & _
           "PurchaseOrderDetail.item_type, PurchaseOrderDetail.Quantity, PurchaseOrderDetail.UnitPrice, Materials.openingBalance " & _
           "FROM (PurchaseOrder INNER JOIN Suppliers ON PurchaseOrder.sup_ID = Suppliers.sup_id) INNER JOIN (Materials INNER JOIN PurchaseOrderDetail " & _
           "ON (Materials.item_type = PurchaseOrderDetail.item_type) AND (Materials.item_code = PurchaseOrderDetail.item_code)) " & _
           "ON (PurchaseOrder.RefNo = PurchaseOrderDetail.RefNo) AND (PurchaseOrder.PO_ID = PurchaseOrderDetail.PO_ID) " & _
           "WHERE (((PurchaseOrder.PO_ID)=" & sPO_ID & ") AND ((PurchaseOrder.RefNo)=" & sRefNo & "));"

 

[namliam]

First lets try and make this a little more readable...

strSQL = ""
strSQL = strSQL & " SELECT PurchaseOrder.PO_ID"
strSQL = strSQL & " , PurchaseOrder.RefNo"
strSQL = strSQL & " , Suppliers.sup_name"
strSQL = strSQL & " , Materials.Productname"
strSQL = strSQL & " , Materials.unit"
strSQL = strSQL & " , PurchaseOrderDetail.item_code"
strSQL = strSQL & " , PurchaseOrderDetail.item_type"
strSQL = strSQL & " , PurchaseOrderDetail.Quantity"
strSQL = strSQL & " , PurchaseOrderDetail.UnitPrice"
strSQL = strSQL & " , Materials.openingBalance"
strSQL = strSQL & " FROM (           PurchaseOrder "
strSQL = strSQL & "       INNER JOIN Suppliers ON PurchaseOrder.sup_ID = Suppliers.sup_id)"
strSQL = strSQL & "       INNER JOIN (Materials "
strSQL = strSQL & "       INNER JOIN PurchaseOrderDetail"
strSQL = strSQL & "                            ON (     Materials.item_type = PurchaseOrderDetail.item_type)"
strSQL = strSQL & "                                AND (Materials.item_code = PurchaseOrderDetail.item_code))"
strSQL = strSQL & "                    ON (     PurchaseOrder.RefNo = PurchaseOrderDetail.RefNo)"
strSQL = strSQL & "                        AND (PurchaseOrder.PO_ID = PurchaseOrderDetail.PO_ID)"
strSQL = strSQL & " WHERE (((PurchaseOrder.PO_ID)=" & sPO_ID & ")"
strSQL = strSQL & "    AND ((PurchaseOrder.RefNo)=" & sRefNo & "));"

Assuming sPO_ID and sRefNo are variables from elsewhere in this code, I cannot (off hand) see anything wrong with this SQL...

Even a small re-write, to increase 'normalization' of the query... Dont show me any problems.
Whats the error your getting?

strSQL = ""
strSQL = strSQL & " SELECT PurchaseOrder.PO_ID"
strSQL = strSQL & " ,      PurchaseOrder.RefNo"
strSQL = strSQL & " ,      Suppliers.sup_name"
strSQL = strSQL & " ,      Materials.Productname"
strSQL = strSQL & " ,      Materials.unit"
strSQL = strSQL & " ,      PurchaseOrderDetail.item_code"
strSQL = strSQL & " ,      PurchaseOrderDetail.item_type"
strSQL = strSQL & " ,      PurchaseOrderDetail.Quantity"
strSQL = strSQL & " ,      PurchaseOrderDetail.UnitPrice"
strSQL = strSQL & " ,      Materials.openingBalance"
strSQL = strSQL & " FROM             PurchaseOrder "
strSQL = strSQL & "       INNER JOIN Suppliers 	                ON PurchaseOrder.sup_ID = Suppliers.sup_id"
strSQL = strSQL & "       INNER JOIN PurchaseOrderDetail        ON PurchaseOrder.RefNo = PurchaseOrderDetail.RefNo"
strSQL = strSQL & "                                            AND PurchaseOrder.PO_ID = PurchaseOrderDetail.PO_ID"
strSQL = strSQL & "       INNER JOIN Materials                  ON PurchaseOrderDetail.item_type = Materials.item_type"
strSQL = strSQL & "                                            AND PurchaseOrderDetail.item_code = Materials.item_code"
strSQL = strSQL & " WHERE  PurchaseOrder.PO_ID=" & sPO_ID & ""
strSQL = strSQL & "    AND PurchaseOrder.RefNo=" & sRefNo & ";"

 

[firoz.raj]

can you tell me in my material table itemcode together with itemtype is a unique.i want to see the max itemcode.in the following way suppose last no is 542 and itemtype is m.then in query 542 is come.same way last no is 543 and itemtype is p.then 543p.So since itemcode together with itemtype is a unique.so let me know the idea.any help would be highly appreciated.Kindly find the attachment also.the following way last itemcode is comming.but suppose last unique term is 543p then 543p should come.along with itemtype .

SELECT MAX([item_Code]) AS max_number
FROM Materials;

 

[namliam]

Your 2 columns combine to be unique arent they?

Or do you want to combine the two columns into one? which you would do like so:
ItemCodeAndType: Item_code & Item_type

Or do you simply want the maximum number per type?
SELECT itemtype, MAX([item_Code]) AS max_number
FROM Materials
group by itemtype;

?? Your question isnt exactly clear or well formatted so I am left confused.
Also the minimum of 'effort' would atleast be to use capitols where applicable and spacing and such

 

[firoz.raj]

Can you demonstrate any example of Append query of Ms Access.Kindly let me know the idea.Any help woud be highly appreciated.i want to see
how can i use the append query.Kindly find the attachment also.kindly let me know the idea.Any help would be highly appreciated.

 

[namliam]

O M G

Erm, you make it append like your doing, it will ask what table to append to, you fill in the table.

Then in the design there will be an extra line "append to", here you fill in the columns where the data needs to go... thats all there is to it.

 

[firoz.raj]

why the following query is not working ?.i want to see the Maximum no of itemcode.records on the basis of parameter.if i put p.it show those records which itemtype p.in the same way if i put M.it should show the Maximum No of itemcode on the basis of sItemType parameter.Which is matching with m.means Kindly find the attachment also.and let me know the idea.in the moment nothing is comming when i put M,P OR T as a parameter.Sql itself is explaining what is needed to do.

PARAMETERS sItemType Text ( 50 );
SELECT Max([Materials.item_code]) AS max_number
FROM Materials
WHERE (((Materials.item_type)='" & sItemType & "'));

 

[namliam]

If you have a text parameter there is no need to quote it, you only need do that if you have a variable that your substituting not with a parameter...

WHERE (((Materials.item_type)=[sItemType]));
Will make it work just fine.