I’m trying to create a budgeting tool for my company. We operate in many countries and budget at the country level so I’ve set up a one-to-many relationship between my tblCOUNTRY and my tblEXPENSE. My tlbEXPENSE has fields ExpenseID, CountryID, Month, and Value.
In order to create a form where each country manager is able to fill out, and make changes to their budget for the next year I would like to create a crosstab query so they can have a horizontal view of the year broken out by month. The problem I’m running into is that crosstab queries are aggregates so they do not allow data entry.
The only solution I’ve thought of is to create a make table query from the crosstab query, but I still run into several problems. First, I need all twelve months to show on my crosstab even if there is not an expense yet for that month, and once I have it broken out by month how can I transpose it back into a form to be able to append it to my tblEXPENSE?
I don’t know if this is even possible with the way I have set up my tables. I guess first I’m wondering if I should change the setup of my tblEXPENSES so it has a field for each month although this would make it harder to do an annual budget rollup based on country? Or should I make a form in Excel for all the country managers to fill out, compile all the data, and append it to my database? Thanks in advance for any advice.
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.
Basically what I am trying to do is to link the crosstab query to a Form. the crosstab query has the following SQL:
TRANSFORM Sum(SubQryTotalProductSales.SumOfQtyOrdered) AS SumOfSumOfQtyOrdered SELECT SubQryTotalProductSales.fkProductID, SubQryTotalProductSales.ProductName FROM SubQryTotalProductSales GROUP BY SubQryTotalProductSales.fkProductID, SubQryTotalProductSales.ProductName, SubQryTotalProductSales.YrOrder ORDER BY SubQryTotalProductSales.YrOrder DESC PIVOT SubQryTotalProductSales.YrOrder In ("2015","2014","2013","2012");
The Main form was made from my Product table and contains all the product related fields. ProductID, ProductName, Description, X_Ref, ListPrice, OurCost, fkSupplier, Discontinued, ReorderLevel etc....
Then I made a Subform with the crosstab query, and tried to place it in the main form. So far everything seemed to work. The next thing was to make the Child and Master Link, I used the fkProductID from Child (Subform), and the ProductID from Master. So there are no errors, but I don't get the link to work, that is when I change the product Field in the Master the Subform does not change accordingly.
When I change the view to design view and then change again to Form view, it opens up with the correct product in both Master and sub. Except in the Sub the productName Shows the productID instead of product Name.
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date FROM [rpt LEAD TYPE SUCCESS RATE2] GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime; TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy"))) GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block PIVOT PRODUCTION.ACTIVITY; //Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
I have Form A that has a combo box and a "Submit" button. When the user clicks on the Submit button it needs to open Form B. Recordsource of this Form B is "Query B"
Item selected on the combo box becomes the criteria for "Query B and Form B needs to be opened based on that criteria.
What is the easiest and fastest way (no dlookups please!) to do this?
If an illustration is possible through an example that will be wonderful.
I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
I have a form that is linked to a query by its Record Source.In one of the Text boxes I want to appear the number of rows in the query where Days = 4 so I thought I would try to use DCOUNT as the Control Source for the Text Box. However every expression I try for DCOUNT throws back error? or name?. How to sort my code?
=DCount("[EPISODEUNIQUEKEY]","1-12 Show Records by Date Range","[Days] = '4'")
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source: [qry_op500_entry_delta]![countofphone model] I get an error stating that this is not in my field list for the form. If the answer is No or if needed I can give more information on what I am trying to do.
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code: Dim ForNm as string Dim Qry as string ForNm = "PersonalFm" Forms(ForNm).Recordsource = Qry Forms![PersonalFm].Recordsource = Qry [Forms]![PersonalFm].Form.Recordsource = Qry
I am setting up a generic form template that will enable me to maintain data in a standardised way. All generic code is stored in a module. There is very little code specific to the table being maintained. Much of the form functionality is determined by data held in supporting tables therefore data driven.
When a form is loaded a commonly named query is created that is used as the Record Source of the form. This query only ever returns one record and uses the value of textbox on the form as criteria in the unique id field.
Despite immediately creating the query in the Open event of the form, Access tries to process the form first and returns an error as the Record Source query applies to another form, the one last opened.
How can I create the query before anything on the form is referenced / processed without creating it before the form is opened?
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE;
----------- qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
Hello All, Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems 1-When I have selected the report and click the preview button. Report is not opened. 2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex: Table contains the following details:
Name Branch Bob 111 Bob 222 Joe 333 Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2 Bob 111 222 Joe 333 Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel
Any help on this would be great
Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea
I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?
I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)
I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?
