Code:
Branch Officer1 TotalLoans1 Officer2 TotalLoans2 Officer3 TotalLoans3 . . .
1 ABC $5,678,555 DEF $6,678,555
2 GHI $7,678,555 JKL $8,678,555 MNO $9,678,555
There is an indeterminate and ever-changing possible-number-of-Officers per Branch.
If this is not possible using queries, is there a better way than writing code to loop through the recordset to get the desired output?I've done it before where I've written output to text and also to a table using VBA & recordset - but I'd like to know if there is a way I can get Access to do the 'heavy lifting'.
Customer Table - CustID Product Table - ProdID CustomerProducts - Contains the CustID and ProdID
I have an "input" form with Customer Name, Address, etc and a subform that allow selecting his/her products.
I want to DISPLAY (only display), in another subform, on this form, (not a report), every customer that has a product in common (even if just one) with the main form customer. Also need it laidout this way:
For example, if Joe Robins has the following products selected: celery, lettuce, tomatoes. The following customers would appear in that subform - because they have ONE or MORE products in common with him:
John Doe (carrots, celery, potatoes) Mike Smith (carrots, radish, tomatoes)
I need to run a query that returns the number of bookings made for each week so far this year. However the date is held in DD/MM/YY format. I had a similar query that worked fine for monthly grouping using Month([Start Date])AS FilterMonth but tried replacing month with week, this didn't work.
Below is a simplified example of what I'm trying to achieve with a parameter query.
The source table for the query contains two fields:
Reading_Date (short date) and Use_value (integer)
The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:
SELECT Sum(Table1.Use_Value) AS SumOfUse_Value FROM Table1 WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));
This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.
Rank # Name Div Sex Score 1 8 Bird Twitty 52Kg Female 684.69 2 12 du Toit Carin 52Kg Female 608.77 3 13 Colbert Jackie 52Kg Female 607.62 4 11 Wade Jenna 52Kg Female 595.41 1 7 DeDee Jenna 67.5Kg Female 702.27 2 10 Diva Meisie 67.5Kg Female 550.46 1 5 Man Super 70Kg Male 655.09 1 9 Doe John 82.5Kg Male 480.3 1 3 Man Spider 90Kg Male 537.63 1 6 America Mr 100Kg Male 406.42 1 4 Hulk Incred 110Kg Male 645.8
SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.ScoreTotal > History.ScoreTotal) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Class.Description AS CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotal FROM Class, Lifter, Team, History, Meet, Activity WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND History.ScoreTotal > 0 AND Class.uidClass = Lifter.uidClassRef AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.uidLifter = History.uidLifterRef AND Team.uidMeetRef = Meet.uidMeet AND Lifter.Gender = Activity.Code GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreC, Lifter.Gender, Activity.Description, Activity.Activity, Lifter.LifterStatus, History.ScoreTotal HAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0) ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC, History.ScoreTotal DESC
I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division. I know that I need a select in the WHERE part but how?
I am completing a grouped report. For each group I would like to cut off the number of entries displayed at 3. I would rather not do this in the query because more than one report uses this query. Id there away of changing the report settings?
I have a check box list on my form for all 50 states, so the user can choose any number and any combination of possible states, which I need the user to be able to do, but when it displays the data it does so horzontally and delineated by a comma. The problem with this is that if enough states are selected, it eventually gets cut off at the end and doesn't display the last states. Is there any way to make it instead show vertically, since at least with that I can format around it and not have a horizontal bar going all the way across the form?
Product1 5 240 A Product1 7 19 B Product1 6 12 C Product2 96 0 A Product2 98 23 B Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
Hopefully this will be something new that someone will be willing to try since I haven't been able to find anything like this in the forum....
We have 7 pieces of equipment (routers/switches/encryptors) that all have large text file configuration data. What we want to do, if possible, is to create an option group and a memo block, so that when we click any one of the pieces of equipment in the option group, the text configuration file will display in the memo block and we will be able to save it as part of the record. Is this even possible or is there a better solution?
This would be the equivalent of simply using a command button to launch notepad with the configuration data.
When I query my database I want to return groups of records. The groups differ from one another based on one field (Employee ID) -- So one group with Employee ID# 1 may have 2 records and another group with Employee ID# 2 may have 5 records, etc. My question is: What is the best way to display these groupings and allow the user to scroll or navigate thru the different groups easily? Any help is appreciated.
I have an access form that displays some data about customers and their booking for flight. so lets say if there is a group of five people that made a booking for a certain flight, i have to assign ticket numbers to them and store it in some table.
Now i can display the number of people in a group for a certain flight in a subform, i want to have another textbox in front of their names so i can type in the ticket numbers. So i went to design view and added another textbox, but the problem is if the subform in displaying 5 rows (for 5 customers) when i type in a ticket number for one customer, all the following rows gets and displays the same ticket number. How can i type something and let it not be repeated infollowing rows.
Hey guys- I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this- I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another... Thanks!
:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
I have been doing a lot of searching on the forums, and I can't seem to find an answer to my problem.
I have a form that uses a query to create an e-mail. This e-mail lists all of the different tests to perform on a sample of a product. My table set up is as follows:
tblProducts ProductID ProductName
tblTests TestID TestName
tblSamples SampleID ProductID TestID
The purpose of this is to send out a sample of a specified product and perform 1 to XX tests on it.
I did a search and i found a thread that seemed like it would answer my question but for some reason the attachment isnt working. :(
Okay heres the question..
I have a form and it contains 3 fields and 3 command buttons. When i hit the command button it uses a query to display the results. What i want to do is instead of having the results display in a new window, i would like it to display on the same form .. maybe on the bottom of it. Should i use a subform or listbox/combobox..
If there's a post out there that addresses this, please point me in that direction as I've had no luck.
I have my queries listed in a table. On my form I have them listed in my combo box. The user selects the query they want and hits the command button "Run". I would like to show the selected query in a subform on the main form and then give the user the option of selecting another command button to export the query to Excel.
I thought it would be as simple as setting the recordsource of the subform to the query name in my combo box, but that doesn't work.
Current code to run query in separate window:
Command button "Run" Private Sub Command9_Click() DoCmd.OpenQuery Me.Combo2.Column(1)
Attempt at getting what I want:
Privat Sub_Command9_Click() Forms!frmQuerySelection!sfrmQuery.Recordsource = Me.Combo2.Column(1) or perhaps sfrmQuery.Recordsource = Me.Combo2
I have created a query with parameters keyed in from unbound textboxes on a form. Does anyone know of a way to display the results (queried) in the sub-form of the same form (main) the textboxes reside? I have the query set up to create a table but when I use that table (or the query itself) in the subform I get the error message:
"The database engine could not lock table "tablename" because it is already in use by another person or process."
This seems appropriate because I am querying for results, but is there any way to get the results of a query into a form, rather than the more common pop up datasheet? I have tried several macros to open and close the tables - queries - copied tables ..re-opened forms....etc, but can't get it to work.
Thanks, for any help, I have spent some time on this one!!!
I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])
My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).
2013 March 9 2013 March 10 2013 March 11 2013 March 12 2013 March 13 2013 March 14 2013 April 14
Hi, can anyone tell me how to doubleclick on field (on a form) and it opens up a form that shows bounded filtered results. I have a sub form which has details supporting summary numbers. I want my user to doubleclcik on the currency field and the detail supporting that sum total, pops up automatically. Any help please? :confused:
Hi, I have created a form (Form1) based on a table (Table1) and also a Query based on this table (Query1). I calculate a field in the query (Expr1: [column1] + [column2]) Now i have created a text box on Form1 that should display Expr1. I know I could also calculate the value directly via the expression builder, but I would prefer to simply display Expr1 from my query. Which is the syntax I have to use in the Expression Builder? I tried "= [Query1]![Expr1]" but it does not work. Thanks for your help Tigrou
