I have used Access for many years occasionally on large projects, but have not become a confident pro in many aspects of Reports. I am overjoyed to find your videos on many topics. I appreciate your style and look forward to learning from you. Thank you for your time efficient, no drama style. (I hope your cat is doing well but you didn't mention it!)
One of the best explanations of Access reports I've seen. Thank you! Question: How can you use a "multivalued Field" in access and have the choices show up in a report or a query? I'm trying to help a friend in the library with her database of stories, songs and themes for little kids.
Thanks for the feedback! Ok, good question. The short answer is: don't use multivalued fields if you can avoid it. A much better approach, and one that works well with Reports is to make yourself a separate table. Say you have a table of books that has an Autonumber key (1,2,3 etc) called BookID. One row per book. Also, you have a table of authors also with an Autonumber key called AuthorID. When you want to add the authors to the book, just have a third table that has its own Autonumber key BookAuthorID, the AuthorID, and the BookID as columns. Then you can have all the authors assigned to the right books and vice versa, with no duplication. You can use this approach for anything that you might use a multivalue field for and it will work nicely on reports and other queries. Whenever you want to see everything together, just drag all three tables into the query view then use that query for your Report. Give it a try!
Not going to lie, I have largely ignored the reports feature of MS Access. I want to learn modern applications for the reports to support visuals in the forms. Appreciate your video!
Fair enough - I think many people want to be able to use powerful online visuals like you can find in Power BI. Access does have Modern Charts which are neat and can be used for some great visuals too but they don't compare to Power BI; I'll cover these in a future episode. IMHO where reports shine is in day-to-day operations, printed materials for meetings, etc. Without some styling they definitely look old these days! Thanks for the feedback!
Yes you can - you can add all kinds of graphics to the reports, including photos. You may need to test out different settings on the image control, ie. sunken, flat, border/no border etc. These are all in the properties.
Thank you, now I've got a solid general idea about MS Access reports. One question please, how can I make a query field clickable as hyperlink to open tge request details form
Glad you enjoyed it! For your query, you probably want to make a form, then add a click or double-click event to your field. If you want to open and view in the same manner like your query, choose the datasheet view or continuous forms. It is a form but it looks like your query. Then you can click on a field and have other forms open etc.
I'm looking for help to create a template. I have 5 different Word files that I'd like to be accessible through buttons in an Access database. The buttons should open specific pages in the Word files for editing and printing. Additionally, I'd like to keep a record of all my prints in a database. Is there anyone who can assist me with this?"
That's a pretty neat project! You can certainly do some interop from your database, using the Word libraries in VBA. it would be similar to my video where I opened another database from the current one, then did some things there before closing and resuming: ruclips.net/video/hhPrBMLNiDs/видео.html except, you will reference the Word libraries in order to get that functionality. Good idea for a video!
. I have a problem. I have two separate columns in a single table query. One for Group A and another for Group B. As I need an overall ranking, need to merge these two columns to one in order sort them from largest to smallest. Shall be grateful if any assistance is provided. Regards.
Just make a query column MyColumn: [Group A] & ", " & [Group B] Then choose ascending for that column. You could also use some other delimiter instead of ", "; maybe " - " or whatever you like.
@@seanmackenziedataengineering Thnx Sir. But it puts the data from both the column to a new column and separates them with a comma. Not fresh row for the new record. If there is not another row, I cannot sort. Kindly see if there is a solution to this. Regards
@@satyabanukil779 Ahh I see now, I misunderstood. What you need is a Union query: Select ID, Field1 As MyField From tblMyTable Union Select ID, Field2 As MyField From tblMyTable Order By MyField
@@seanmackenziedataengineering @TLDW_Tutorials Thank you Sir. I tried with Union Queries. But it is returning exactly the the field name which I wrote not the data under it. For example, My code is " SELECT Query1.T1TT AS TeamTotal, 'P1' AS TeamId, "TEAM1" AS Team FROM Query1 GROUP BY Query1.T1TT UNION ALL SELECT Query2.T2TT AS TeamTotal, 'P2' AS TeamId,"TEAM2" AS Team FROM Query2 GROUP BY Query2.T2TT" and so on. But when I execute the code the dataset shows just "TEAM1", "TEAM2" and not the data under these column. So I may be wrong with my syntax since it is a text field. If you get time, kindly help. Regards.
You may have your fields in the Detail section, but perhaps those need to be in a group section, with the Detail section collapsed (so you only get one page), or you can create a summary query first then create a report on that (in that case, put the fields in the Detail).
I hear you! Power BI is pretty awesome and I use it all the time. Did you see how it shot out in front of everyone on the Gartner quadrant? MS must be doing something right.
@@gerfer6261 Some interesting implementations here: stackoverflow.com/questions/125785/password-hash-function-for-excel-vba One accesses .Net libraries through CreateObject which simplifies things. You can start looking there and see what works for you.
I have used Access for many years occasionally on large projects, but have not become a confident pro in many aspects of Reports. I am overjoyed to find your videos on many topics. I appreciate your style and look forward to learning from you. Thank you for your time efficient, no drama style. (I hope your cat is doing well but you didn't mention it!)
Thanks for sharing - and happy to have you onboard. Cheers
One of the best explanations of Access reports I've seen. Thank you! Question: How can you use a "multivalued Field" in access and have the choices show up in a report or a query? I'm trying to help a friend in the library with her database of stories, songs and themes for little kids.
Thanks for the feedback! Ok, good question. The short answer is: don't use multivalued fields if you can avoid it. A much better approach, and one that works well with Reports is to make yourself a separate table. Say you have a table of books that has an Autonumber key (1,2,3 etc) called BookID. One row per book. Also, you have a table of authors also with an Autonumber key called AuthorID. When you want to add the authors to the book, just have a third table that has its own Autonumber key BookAuthorID, the AuthorID, and the BookID as columns. Then you can have all the authors assigned to the right books and vice versa, with no duplication. You can use this approach for anything that you might use a multivalue field for and it will work nicely on reports and other queries. Whenever you want to see everything together, just drag all three tables into the query view then use that query for your Report. Give it a try!
Not going to lie, I have largely ignored the reports feature of MS Access. I want to learn modern applications for the reports to support visuals in the forms. Appreciate your video!
Fair enough - I think many people want to be able to use powerful online visuals like you can find in Power BI. Access does have Modern Charts which are neat and can be used for some great visuals too but they don't compare to Power BI; I'll cover these in a future episode. IMHO where reports shine is in day-to-day operations, printed materials for meetings, etc. Without some styling they definitely look old these days! Thanks for the feedback!
Thanks for the great effort! I have a question if you don't mind. Is it possible to print a report with a photo in the header section?
Yes you can - you can add all kinds of graphics to the reports, including photos. You may need to test out different settings on the image control, ie. sunken, flat, border/no border etc. These are all in the properties.
Your tutorials are pro, thanks.
Glad you like them! cheers
Very helpful!
Glad you think so!
Thank you, now I've got a solid general idea about MS Access reports.
One question please, how can I make a query field clickable as hyperlink to open tge request details form
Glad you enjoyed it! For your query, you probably want to make a form, then add a click or double-click event to your field. If you want to open and view in the same manner like your query, choose the datasheet view or continuous forms. It is a form but it looks like your query. Then you can click on a field and have other forms open etc.
I'm looking for help to create a template. I have 5 different Word files that I'd like to be accessible through buttons in an Access database. The buttons should open specific pages in the Word files for editing and printing. Additionally, I'd like to keep a record of all my prints in a database. Is there anyone who can assist me with this?"
That's a pretty neat project! You can certainly do some interop from your database, using the Word libraries in VBA. it would be similar to my video where I opened another database from the current one, then did some things there before closing and resuming: ruclips.net/video/hhPrBMLNiDs/видео.html except, you will reference the Word libraries in order to get that functionality. Good idea for a video!
thank you youre the best
Thx!
. I have a problem. I have two separate columns in a single table query. One for Group A and another for Group B. As I need an overall ranking, need to merge these two columns to one in order sort them from largest to smallest. Shall be grateful if any assistance is provided. Regards.
Just make a query column MyColumn: [Group A] & ", " & [Group B]
Then choose ascending for that column. You could also use some other delimiter instead of ", "; maybe " - " or whatever you like.
@@seanmackenziedataengineering Thnx a lot Sir. Regards
@@seanmackenziedataengineering Thnx Sir. But it puts the data from both the column to a new column and separates them with a comma. Not fresh row for the new record. If there is not another row, I cannot sort. Kindly see if there is a solution to this. Regards
@@satyabanukil779 Ahh I see now, I misunderstood. What you need is a Union query:
Select ID, Field1 As MyField From tblMyTable Union Select ID, Field2 As MyField From tblMyTable Order By MyField
@@seanmackenziedataengineering @TLDW_Tutorials Thank you Sir. I tried with Union Queries. But it is returning exactly the the field name which I wrote not the data under it. For example, My code is "
SELECT Query1.T1TT AS TeamTotal, 'P1' AS TeamId, "TEAM1" AS Team FROM Query1 GROUP BY Query1.T1TT
UNION ALL
SELECT Query2.T2TT AS TeamTotal, 'P2' AS TeamId,"TEAM2" AS Team FROM Query2 GROUP BY Query2.T2TT" and so on. But when I execute the code the dataset shows just "TEAM1", "TEAM2" and not the data under these column. So I may be wrong with my syntax since it is a text field. If you get time, kindly help. Regards.
My report which ideally should be only one page is repeating multiple times, it's a summary report. why would that be
You may have your fields in the Detail section, but perhaps those need to be in a group section, with the Detail section collapsed (so you only get one page), or you can create a summary query first then create a report on that (in that case, put the fields in the Detail).
Good feature, but power Bi is leading the I think
But respect the elders
I hear you! Power BI is pretty awesome and I use it all the time. Did you see how it shot out in front of everyone on the Gartner quadrant? MS must be doing something right.
Lead me to somewhere I can hashing passwords in access please 🤭
In audit process they picked on me storing plain text passwords in a table
@@gerfer6261 Some interesting implementations here: stackoverflow.com/questions/125785/password-hash-function-for-excel-vba
One accesses .Net libraries through CreateObject which simplifies things. You can start looking there and see what works for you.