Modify Quarterly Sales Report In North-wind Database?
Jul 30, 2015i 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 Replies
ADVERTISEMENT
North Wind
Mar 29, 2008I downloaded the North Wind template. Is it usabe? I cannot delete the sample stuff. Can you use it for invoices and to receive payment for open invoices? It seems like just a sample and not usable.
Any help
thanks
North Wind
Mar 30, 2008I downloaded North Wind. Are there any guides on using it? Isit usable it wont let me delete anything?
Are the downloadable templates usable are just samples? They never seem to work.
Any help
thanks
Crosstab Quarterly Sales Query
Mar 3, 2007I 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
I Want A Form Like In Northwind Sample Database: Summary Of Sales By Year Report.
Jan 9, 2006Hi,
I have a problem with form design,
I want a form like in Northwind sample database: Summary of Sales by Year Report.
It use Sorting and Grouping for Footer that I can't find it in Form design.
Is it any other way to do it in Form design so I can get the same result like in Report design?.
The reason why I want it, because I want to control the size.
thankyou in advance for your help.
Gunawan.
Modify Query For A Report
Jan 9, 2007Hi all
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
DoCmd.OpenReport myReport, acViewPreview, "", _
"[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
Hope this makes sense
Cheers
Kev.
Calculating MTD And YTD Sales For Report
Dec 1, 2005I have been stuck on this one for a while. I have an Access 2003 Database that I need to calculate the MTD sales and the YTD sales of parts ordered
Here are the relevant tables and thier attributes
Part -
PartNum
Description
UnitPrice
OnHand
ReorderPoint
Orders -
OrderNum
OrderDate
CustomerNum
CustomerPONum
OrderDetail -
PartNum
NumOrdered (Quantity)
QuotedPrice
NumShipped
If I have left anything off that may be vital please ask and I will supply the info. I need this info ASAP though.
I think the answer has something to do with the Date() and CurrentDate() but I have no clue. Please let me first know the queries I need to run and that should be it. I just need to put MTD and YTD Sales in a report.
I REALIZED THIS SHOULD BE A QUERY THIS WILL BE POSTED IN QUERY INSTEAD
How Can I Create A Sales Form That Can Update Both The Product And Sales Tables
Oct 11, 2007Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.
View 1 Replies View RelatedModify Auto-Number Properties In Web Database
Mar 18, 2015I have a web database in access and I am trying to set up a new table. They key piece of this table needs to be a randomly generated ID number that is between 4-6 digits long. I know that when I set up a new table the ID field is set as "AutoNumber" but I can't seem to modify the properties of this field like I can in Non-web databases.
Is there a way to have a randomly generated ID number that is a specific length in a web database? If so, how can I do this (preferably without VBA since that is not Web compatible)?
Modules & VBA :: Module To Modify Report Controls Based On Form?
Feb 12, 2015I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code:
Option Compare Database
Option Explicit
Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
Tables :: Sales Database - Multiple Employees Per Client
Nov 8, 2012I'm still working on that sales database...and I now ran into an issue with multiple employees per client.
I have 2 tables, tbl_Employees and tbl_Clients.
tbl_Employees:
[Employee_ID]
[Employee_Name]
tbl_Clients:
[Client_ID]
[Employee1_ID]
[Employee2_ID]
General :: Split Database - Modify Backend File And Make A New Front End
Jun 12, 2012So a while back I created a database which I use to keep track of my companies large list of products. It is very simple only 3 tables, 1 query and 1 form. After running into a problem with copying and pasting updates on each computer in the office I decided to split the database on a network drive. This worked for a bit, however I had to add new fields and modify the form, which corrupted the file. Luckily I had a backup before the split.
As far as I could tell all I had to do was modify the backend file and make a new front end. However it seems like it is not as easy as I thought it would be. How do you modify a split database without corrupting the files or using a non-split copy?
Quarterly Average
Dec 7, 2004I 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
Query Help On Quarterly Data
Mar 28, 2006Hello, 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
Quarterly, Monthly, Annual Advice
Jan 26, 2008Need 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?
Create Field With Quarterly Growth?
Jun 2, 2014I 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.
Count Of Non-duplicated Clients On Monthly / Quarterly / Yearly Search
Mar 19, 2014I'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 RelatedReports :: Using Form With Command Buttons To Set Criteria - Quarterly Reports
Jul 17, 2015I 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.
Modify All Records
Jun 21, 2007Hello,
I have a form (linked to a table). I have 'txtBoxA' and 'txtBoxB'. Now, each record will have different values in 'txtBoxA' and based on that value 'txtBoxB' will change for that specific record. In the 'OnLoad' event for the form I type the code, but when I re-open the form it only works for the 1st record, and it doesn't change all the rest (records).
Do I need a loop to go through all the records?? I thought that this was done automatically by ACCESS. If I have to use a loop, can anyone please give me the exact syntax?
Please, this is really urgent so I would really appreciate an answer asap
Thank you very much.
How To Modify A Field Value?
Jan 25, 2005Hi,
I am trying to change the value of a field (well a few fields once I get it to work for one!) in a table.
I am trying to update the last record in a table, but cannot get any code to work in VBA for this and cannot make an update query select just the last record in the table to do it that way either!
The VBA I am using is:
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Inventory Transactions", dbOpenDynaset)
With rst
.MoveLast
.Edit
![UnitsOrdered] = (Me.UnitsOrdered - Me.Qty_Passed_Insp)
.Update
End With
rst.Close
db.Close
Units ordered doesn't change though!
For the update query I am trying the following criteria for the transactionID field:
=max( [Inventory Transactions]![TransactionID] )
But this returns the error message:
"Cannot have aggregate finction in WHERE clause ([Inventory Transactions].TransactionID = Max([Inventory Transactions]![TransactionID]))"
Any suggestions?
Thanks in advance!
Modify Query
May 18, 2007Hi,
I create one database and the query and the forms for my clients in the sharing drive. Every time, the user open the form and select the field names and then execute the query depend on which fields the user select on the form, and then output to the screen. Here is the question, if there are multi user access the same form/query, one user pickup 3 fields to run the query, and another user pickup 5 fields to run the query. Becuase one user select 3 field names on the form, and then the code will modify the query contents, another user select the 6 field names on the form, and then the code will modify the query contents.
So, will the query affect each other?
Modify Query
Apr 25, 2006Dim strSQL As String
strSQL = "SELECT [Company Name],[Vendor Name],[Account],[Statement Number],[Amount] from [Statements] "
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.view
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("Statement Informations").Command
cmd.CommandText = strSQL
Set cat.Views("Statement Informations").Command = cmd
Set cat = Nothing
DoCmd.OpenQuery "Statement Informations"
I used the above code to modify the existing query, "Statement Informations".
When I run it, it give out the error message:
Run-time error '3218':
Could not update; currently locked.
And then, I went to queries section, and open the query "Statement Informations" directly, close the query, and then run the code again.
Then, it works.
I don't know why.
If I run it on next machine on other day, then it give out same error.
How can I solve it?
Please let me know, thanks.
Modify Auto ID Increment
Aug 22, 2007Hello,
Can someone tell me how I can change the increment in an existing database.
The PK is currently Auto ID with each new record currently 1001, 1002 and so on.
What I am trying to do is say starting monday the next new entry begins at 5003 versus 1003, in essence I am trying to change my first number to a 5 versus a 1.
Is this possible?
Thanks..
Fen How
Modify Data Using A Query
Jun 1, 2006Hi 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
Can't Modify Values In A Query
Feb 4, 2008Hi!
It has happen to me many times that when I create specific queries I'm then not able to modify the values of em...
Do you know the possible causes of this?
Is it because I'm filtering? Ordering by? Grouping? Using fields of differents tables related 1-many? Calculated fields? Expressions? Modules?
----------
One specific case that has presented to me is a query that returns info from 1(main)-many(details) relationship. When I use the sorting of a field of the main table it doesn't allow me to edit values, tho when I delete this sorting it works fine.
Nevertheless, I also have another query which just returns values from one table and whether I use sorting or not, I can modify the values.
What's happening??? :( :confused:
Can Anyone Modify This Query Statement
Apr 6, 2008hi im a first time user of access,
can anyone modify this query statement...
select IDno, LName + ', ' + FName as Name, course, Status,
(select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno
)
as TotalBooks
,
(select sum(io.Penalty) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno) as Penalties from borrowers b
i also want to set the TotalBooks and Penalties to 0 when their values are null thanks