Query Help On Quarterly Data
Mar 28, 2006
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?
Thanks in advance.
Table 1
Product_#_____Quarter______Volume
____A__________Q1FY05________1000__
____A__________Q2FY05________1327__
Table 2
Product_#_____Quarter________Price
____A__________Q1FY05__________7.34__
____A__________Q2FY05__________7.05__
Desired Result
Product#___Quarter___Volume___Price____Revenue
___A________Q1FY05_____1000_____7.34_____$7340.00
___A________Q2FY05_____1327_____7.05_____$9355.35
View Replies
ADVERTISEMENT
Mar 3, 2007
I have created a cross tab quarterly query to sjow the 4 quarters sales.
Problem is if there is no sales in one of the quaerters, the report based on that query gives an error as per attached photo and do not open.
any work around
View 1 Replies
View Related
Dec 7, 2004
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))
Thanks,
Tim
View 3 Replies
View Related
Jan 26, 2008
Need a little professional advice.
I have a product table. Those products expire over time. Their shelf life is monthly, quarterly, or annual.
What should I use for monthly, quarterly, and annual in my table.
My first thought was 30, 90, and 365. Then in my query I would use the datediff function. If the datediff was >= to ...then ...
However there are months with 28,29, and 31 days. With this quarterly can change as well. The annual as well by 1.
What is the rule, if there is one?
View 9 Replies
View Related
Jun 2, 2014
I need to creat a field named Q.
Q is the quarterly growth from the YYZ field.
I have another field, Date, which is obviously the date of the record YYZ.
There is one record per date.
So, how do i compute
Q=Y(t+3)-Y(t)
?
I'm very lost doing this operation.
View 7 Replies
View Related
Mar 19, 2014
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.
View 2 Replies
View Related
Jul 30, 2015
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.
View 1 Replies
View Related
Jul 17, 2015
I am using a form with command buttons on it to set the criteria in my query to run the report.
Example : cmdQ1 'Command Button
PHP Code:
'1st Quarterly Report.
BeginDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
EndDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0)Â
I managed to get this far but need to continue on with 2,3, and 4th quarters.
View 8 Replies
View Related
Dec 30, 2014
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:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Aug 10, 2012
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.
View 14 Replies
View Related
Dec 17, 2012
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.
View 2 Replies
View Related
Nov 20, 2013
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.
View 5 Replies
View Related
Aug 12, 2014
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.
View 9 Replies
View Related
Nov 28, 2006
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?
Iain
View 2 Replies
View Related
Apr 1, 2008
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.
View 4 Replies
View Related
Mar 29, 2007
Hi there
my current query
INSERT INTO [Transaction] ( booking_ref, transaction_type, transaction_description, transaction_amount, [date/time] )
VALUES (10, 'Booking', 'Hotel Room Booking Charge', 4.5, Now());
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
thanks in advance
View 1 Replies
View Related
Mar 4, 2008
how can i msgbox data from a sql query
Tasklist.RowSource = "SELECT [Tasks].Staff_Name,
WHERE [Tasks].staff_name = "josh"
[Tasklist].Requery
i tryed to use
msgbox Tasklist.Staff_Name
but i get an error message
any ideas
View 1 Replies
View Related
Jun 20, 2005
Hey,
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?
THX
View 1 Replies
View Related
Jan 21, 2007
Please help me.
I want to export a query from a MS Access 2003 to textfile even if the query has no data..
But right now, I can export it to textfile if there is data. But when the query has no data, nothing is exported to textfile.
Can someone teach me how to do it?
Thanks
View 4 Replies
View Related
Sep 22, 2005
I have two field columns
For example:
Field 1 Field 2
-------- --------
Blue Blue
Red Red
Yellow Black
I need to write a select query that will output any rows where field 1 and field do not match.
How can I write this? does anyone have any suggestions?
Thanks
View 1 Replies
View Related
Sep 14, 2006
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.
View 7 Replies
View Related
Nov 28, 2005
Hi.
I have a table that has 1 field called IP
it containes IP addresses as data like this.
IP
255.254.253.252
255.251.250.0
Is it possible to have a query that would break this up so it becomes a new table with 4 fields like this:
IP1 IP2 IP3 IP4
255 254 253 252
255 251 250 0
I'm a beginer in this and I could not find a way so for to break this apart correctly.
Please help.
View 3 Replies
View Related
Jan 4, 2006
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];
View 1 Replies
View Related
Jan 16, 2006
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.
View 2 Replies
View Related
Feb 27, 2006
Good afternoon,
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?
Thanks in advance.
Earl
View 1 Replies
View Related
Jun 1, 2006
Hi all,
Is it possible to modify data using a 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.
Cheets,
Matt
View 2 Replies
View Related
