Hello, I’m new to this board and to Access as well. My Database has two tables. Once contains product data including quarterly volumes. The other contains quarterly pricing data. I’d like to query the pricing data so that it returns the proper price based on the quarter the volume ocurred in. This would then be used to calculate Revenue. How do I write a query to select the correct price based on the quarter value in another table?
I need help with a calculation in my form. I have a form named families. IN this form I have 12 check box's, one for each month. I would like to set up another box which would take the average of the past 3 months and tell me what percentage of the time the box is checked. For example, since it is december, I would like a box named quarterly average to look at the past 3 months, obviously september, october and november, and tell me in percentages what the percentage is that the past 3 months check boxes have been checked. This is the basic code which I created for my unbounded box, but I want it to be dynamic, so that it recognizes what the month is today and tells me automatically what the percentage is.
Control sourceis set to =Abs(([Sep]+[Oct]+[Nov])/(3))
I'm new to Access. It is obviously a very powerful database program I've developed a data base of my agency's service purchases for a variety of clients. I have to generate a report totaling the monthly purchases and counting the number of unduplicated clients. We make multiple purchases for clients monthly. There must be a feature on this program that will sort through the client ID Numbers and count them (rejecting duplicates) in that one month or quarter or year. I've been exporting to excel, sorting by client ID and then counting each new ID number.
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
I am creating a database for a hyperthetical car hire company.
I have a field with a lookup/query. The data that this query searchs for is entered into my table/form already. Is there anyway of avoiding repeating this data twice?
is it possible to repeat a query on data returned by the same query while returning it to a single table?
i have a query that runs on the following info: PartNumber - the part number of an item AsmNumber - the part number of the item the part belongs to IsAsm - true/false on whether or not the part is an assembly
(see attached database)
the current query, when 11135 is used as input, returns 11124, 11165, 11103, and 11155. likewise when 11165 is entered the query returns 11109, 11110, and 11111.
i would like the query to return 11124, 11165, 11109, 11110, 11111, 11103, and 11155 when 11135 is entered.
the sample database attached is stripped down but accurately reflects what i'm working with. i do not know how many sub-assemblies belong to a top-assembly (could be zero, one, or sixteen), therefore i think the 'sub-query' would use the 'IsAsm' value as some sort of criteria - yes?
if what i'm asking is impossible, i'm very open to suggestions as long as they work with the data given.
i appreciate any help (especially being this is my first post) and apologize for the verbose post, i'm not good with brevity.
now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.
I hope someone can point me in the right direction
Can someone pls tell me how can i get data from query? The problem that i have is that i build a query that have a one row resoult. And now i would like to update some fields in my form to the resoult of the query. So lets sey that i have a Field1 to Field5 in my Query and the same way on my form. Now i would like to build a code that will get the data form Query Field1 to Form Field1? I know how to get data from one form to another but i do not know how to get it from a query? So can someone pls tell me that?
Hi Folks I have a form that is based on a query. I want to change the unique ID that is displayed on this form. The new ID is sourced from a different query. I.E. Not the same query that the form derives its information.
I have set the data source property of the field (Combo1) to the "other" query.
How do I get the Combo1 to update to the value of the first record? Currently its defaulting to Null.
I have tried using : Me.Combo1.Requery - This does NOTHING to the vaue. Me.Combo1.MoveNext - I keep getting a "Method or data members not found" error.
I am not sure how this query I made is pulling up wrong data. Everything else is fine, except for 1 row of data. It is supposed to pull how many demonstations were scheduled for that day and how many were executed. It comes up with 4 demos and 8 executed. That can't be right. I checked it with the actual data and there are 6 demos total and 4 executed. Anyone know what could be going on there? For the other 30 something records it works just fine.
SQL: SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, CountOfStatus/[# of Demos] AS Percentage FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo WHERE (((DemoOrder.Status)="E")) GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];
I have a form that has a button. This button is used to open a report. The command used to open the report refers to a query and sets the criteria so the report generated only returns values the button asks for.
I know how to set the criteria of a query using VBA, but is there a way to set a field that has no data to now have data?
I have a field in a query called Type and it is set to Null (SQL: Null As Type) I would like to do this in the VBA, but subsitute the Null for a value that would be specific to the button.
Now I'm not sure whether this probelm is with my report or in my query, so I am posting in both areas.
On a form in my database I've included a look-up field that reference data from anoher table (i.e. the main data on the form comes from one table, and the "look-up" reference information on the form comes from another table). In preparing a report to show the main table data and the look-up information, I've created a query that includes the relevant fields of both tables that are related by the Unique ID [autonumber] field.
My report pulls its information from this query, but the look-up data does not correlate with the main data. What I mean is that while record #1 of the main table might reference record #5 of the look-up table on my form, what actually appears in my report for record #1 of the main table is record #12 of the look-up tale instead.
Does that make sense? In other words, I can't get the main table records to match up with their corresponding look-up table records on my report, eventhough they match up perfectly on the form.
What am I doing wrong? Is there a problem with the query?
Say for instance I have a whole load of IP's in a table in the form of A.B.C.*, where and and B are fixed, C varies slightly and * can be anything from 1-255. Can I use a query to look at an IP and the append an IP record to a different table in the form of A.B.C.0. Basically, modifying the data so that whatever the value of *, it is changed to 0.
