Sir, I have three different fields A1, A2 and A3. Can I bring all these fields under a single group A for the purpose of sorting. As regards Union query, I'm unable to add data from two queries. Didn't find any such solution. For example, in query 1 I have field Number1 and in Query 2 i have a field Number 2. I want too add Number 1 plus Number 2 and show them in the Union query. I tried but it doesn't work as i may not be using an incorrect syntax. Thnx and regards
Something like: Select Query1.ID, Nz(Query1.Number1, 0) + Nz(Query2.Number2, 0) As MySum From Query1 Inner Join Query2 on Query1.ID = Query2.ID; That will add the fields from 2 tables together. Assume ID is your id field that brings them together. It uses Nz to put a 0 in if you find a null. Try it!
@@seanmackenziedataengineering Sorry Sir, I think I couldn't explain my problem correctly. In fact in Query1, I have a field called PAIR and in Query2, I have a field called OPP (both are same team, sometime they are identified as PAIR1 and sometime as OPP1). Now from the two queries, I want to add them up to a fresh Query3. Based on your syntax I tried with the following code in a UNION Query : "SELECT Query1.PAIR, Nz(Query1.PAIR,0)+Nz(Query2.OPP,0) AS MySum FROM Query1 INNER JOIN Query2 ON Query1.PAIR=Query2.OPP ;". But it returns only one information, whereas I want data for all teams like PAIR1 with OPP1, PAIR2 with OPP2 and so on. Regards.
@@seanmackenziedataengineering Sorry Sir, think I have not explained my problem properly. In fact I have teams in a competition. Sometimes the team is identified as PAIR and sometimes the same team as OPPONENT. In my database I have identified them as PAIR or OPP but their ID Number is same. For instance, PAIR1 and OPP1 are the same team but the identity changes from PAIR when they are playing as Opponent. Then PAIR1 becomes as OPP1 and so on. For PAIRs I have a query, Query1 and for OPPs I have another query, Query2. I want to add the data pertaining to all the PAIRS and OPPs (having same id number like PAIR1 or OPP1) in a third query Query3. Based on your advised systax, I slightly modified it according to my need as follows "Select Query1.PAIR, Nz(Query1.Pair1, 0) + Nz(Query2.Opp2, 0) As MySum From Query1 Inner Join Query2 on Query1.PAIR = Query2.OPP;" But it brings me only one data pertaining to PAIR1 or OPP1. But I want it should add all PAIRs and OPPs (corresponding to their number e.g. PAIR1+ OPP1, PAIR2 + OPP2, PAIR 7 + OPP7 so on.). I also tried this "SELECT Query1.TOT1 AS TeamTotal, PAIR1 AS TeamId1, P_NAME AS Team1 FROM Query1. GROUP BY Query1.TOT1, Query1.T1 UNION ALL SELECT Query2.TOT21 AS TeamTotal2, OPP2 AS TeamId2,OPP_NAME AS Team2 FROM Query2, GROUP BY Query2.TOT21, Query2.T2; But none of the above are working. So solicit your kind advice as how to overcome. Thnx and Regards.
hi my question is how i add this property to my form "not my report":: i want that my records in the form that have same bill number shows near each other ?? thx
Good question! Forms do not have grouping, but they do have sorting. Open form in design view, go to form properties, set Order By to bill number field, set Order By On Load to Yes. Good luck!
Sir, in a tabular form, after sorting a second field (one field already sorted as working correctly), after reopening the form, I observed 10 is coming after 1 and not after 9 as it should be. Is there any way to sort it numerically. Regards
There are a few ways to fix it. On the source query, you can create another field which is MyField: Clng([SourceField]) which will convert those entries to a number. Sort by that new field. Or, leave it as a text field but pad it with zeros so that it sorts correctly. 9 becomes 09 so that sorts correctly with 10, 11 etc. If the number never goes as high as 100 then MyField: Right("00" & [SourceField], 2)
@@seanmackenziedataengineering Thank you Sir once again. Just one more point to be clear. In your reply you mentioned "source query". But I have not gone to any query. Just one table with a tabular form as mentioned. My question is if I add a new text field in the table and add it to the same tabular form and write the code( e.g. clng([new text field]) and sort it, will it serve my purpose. Thnx and regards
@@satyabanukil779 Just make a query on your table. The query is Select * From MyTable. But then add a new field as I mentioned and you have a nice way to sort. Change the RecordSource of your form to your new query. Since it is based on a single table only, it will still be updateable.
@@seanmackenziedataengineering Thank you Sir. It is working perfect. I would say that nearly a year ago with the help of youtube tutorial built a module and named it as "GREATESTVALUE". On a form, I have three different numeric fields and put the formula like this; Field4 = GREATESTVALUE(Field1, Field2, Field3) which works properly and in field4 it shows the maximum of those 3 fields. As you know in form level, cannot put a code like this. Field4 = Sum(Field1, Field2, Field3) so that it calculates the total of all the three fields. But if make a module, probably my purpose will be served. My question is it possible in MS Access.
