I'm trying to create a simple sales query by manufacture by month. I want the month as the column headers (with a total YTD Column). Mfg's are the row headers. The query is already completed and I figured out how to do the row total but can't figure out how to create total column. Can I not add a total column in design view? How would I do that?
View RepliesI'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column
(2) [shift+arrows] to select all of the columns I need
(3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Do any of you know a way I can make this work?
I'm using the following expression to find the sum of the current month sales.
Code:
=DSum("[Price]*[Quantity]","QueryCurrentMonth Sales","[ORDDate]=Month (now())")
However, it returns no result. The above expression is located on the control source of an ab textbox. Moreover, it works well when i try the following expression
Code:
=DSum("[Price]*[Quantity]","QueryCurrentMonth Sales","[ORDDate]=[ORDDate]")
It returns the sum of all sales.
On the surface this seems easy, but I am struggling. two tables
tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months
So I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. I cannot determine the correct query formula.
I've created an access chart to show total sales ordered by customers.
I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.
This query is then used on a report to create the graph.
Finally there is a button on my form that prints this report.
My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.
If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.
*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.
I have a query where I want to see a percentage of executed demos. I currently have Name of the promo (group by), # of demos (group by), Status (criteria = E for executed demos, count) and Percentage: Status/# of demos. Everytime I run this query I get this message: "Data type mismatch in criteria expression." The only data in Status is either a O for open or an E for executed. The E in Status is the only criteria I have in the whole query. If I take the count function off Status, it runs, but does not give the right results. Does anyone know what I am doing wrong?
View 14 Replies View RelatedWe are opening up a database that was made in 2003 with 2007. The design view gives us an error of "'' is not a a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long" Does anyone have a solution for this problem?
View 14 Replies View RelatedIn some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
I have a Form which I have linked correctly to a subform. The Text boxes are showing in the Design view but are not when one switches to the Form View. Labels for Fields are visible in the Form View. Have even created a new subform and that will also not display the Text Boxes.
View 1 Replies View RelatedWhy the ability to view the properties of an object within a form is not available when you double click on it in design view?
I was happily working away double clicking on a command button to edit some code when for some reason the next time I tried to edit it did not open up for me.
I was unable to access it even by right clicking on the object & selecting properties as that also appears to be disabled, not greyed out or anything but just does nothing when selected.
Have I inadvertently changed a setting somewhere that prevents the properties from being displayed?
I am adding fields with numeric values in design view of my query. The only issue I am having is that the query is not showing totals when one of the fields does not have a numeric value in it. How can I show the total numeric value regardless of the null value? Thank you:cool:
View 1 Replies View RelatedI need constructing a find unmatched query. I don't really know what I'm doing with SQL and I think that I can only take things so far with query design view.
I have two tables. T_Productions and ProPro (see attached). In Access query design, I can only specify one matching criteria. I need a query that will show me records from T_Productions that don't have matching Production_Code (PpNum in ProPro), Avantage_Product_Code (PpPrNum in ProPro) and Step (PpEtape in ProPro) in ProPro.
What I have now only gives me the records where Avantage_Product_Code don't match PpPrNum.
Code:
SELECT T_Productions.ID, T_Productions.Production_Code, T_Productions.Avantage_Product_Code, T_Productions.Step, T_Productions.Quantity_Required
FROM T_Productions LEFT JOIN ProPro ON T_Productions.[Avantage_Product_Code] = ProPro.[PpPrNum]
WHERE (((ProPro.PpPrNum) Is Null));
I am trying to copy the design view for queries into either MS Word or Excel. It seems like it should be simple but Access doesn't seem to want to let me copy the table. I have a lot of criteria (and a lot of databases I am working with) and we are auditing our files and creating knowledge preservation documents to make sure no information is lost over time.
I am trying to copy the table that lists everything like below (and in the attached screenshot)
Field
Table
Total
Sort
Show
Criteria
Copy to excel or word.zip
Anybody ever heard of this before? I open a form, and it is empty, just a blank window. I open the same form in Design View, all appears well. :mad:
View 2 Replies View Relatedhow to have our Access 2003 query total our report column "ProfitLoss`and produce on our report a Total Value of -$420.17
View 13 Replies View RelatedI have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.
I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like
CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject
I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross
:confused: :confused: :confused:
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
IDENTITY:"TEXT(ROW(A17),"-0")"
What am I doing wrong?
I have a table called NewTransactions_tbl that I have been using for some time. I am trying to add a new field called CustomerProjectNumber. It's not being displayed when I look at the table view. I've attached an image showing the settings for the field. I am probably missing something obvious.
Any ideas why it is not showing?
I am using Access 2000.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
Thanks
Rahul
My issue is that one of my queries can be run, opened in SQL view, but not opened in design view. When I attempt to view in design view, Access freezes and I am forced to close the program. My database is split, FE on my local, BE on another computer on my LAN.
Below is the SQL, I tried to just copy and paste this code into a new query and I encountered the same issue.
Code:
SELECT tbl_SurveyJobs.job_number, tbl_JobTypes.job_type_description, tbl_Clients.company_name, tbl_SurveyJobs.start_date, tbl_SurveyJobs.end_date, tbl_SurveyJobs.comments, tbl_SurveyJobs.contact_notes, tbl_SurveyJobs.regplan_number, tbl_SurveyJobs.sheet_number, tbl_SurveyJobs.mplan_number, tbl_SurveyJobs.lot_block, tbl_SurveyJobs.township, tbl_SurveyJobs.concession, tbl_SurveyJobs.township_lot,
[Code] .....
trying to create an update query to Budget table using the Access Design View:
Field: PctSls (in tblBudget) Update to: [Expense] / [Sales]. The update query always returns 0. However, if I create a Select query using the same calculation, the correct results is displayed. PctSls is defined in the Budget table.
I haven't worked with Access (2003) for several years but this seems too simple to be causing me such frustration. (Was only a casual user even then).
tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
View 2 Replies View RelatedYou know how normally in design view there would be a table at the bottom of the screen where you can drag your fields from the view above.
Well, I have a query where this table view has disappeared ?
I can edit the query in SQL view, the query works, just the table view is missing.
Ever happened to anyone else ? I've done the obvious and scrolled down as far as I can and nope, it's not there ??? :confused:
I am wondering why the alteration to my form in design view is not reflected in the "normal" form view. I have included the two views. In design view I moved "Staff In"/"Staff ID" to the top of the form believing that as I work left to right thay would appear on the left hand side in normal view.
However, in the image of "normal" view you can see that "Staff In" is way over on the right hand side of the form.
Any ideas how I can fix this?
Thank you
http://www.btinternet.com/~peter_paterson/Access/SubFormDesign.jpg
http://www.btinternet.com/~peter_paterson/Access/SubformFormView.jpg