Crosstab Query As A Form Source
Apr 21, 2005
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.
View Replies
ADVERTISEMENT
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
Sep 20, 2015
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.
View 12 Replies
View Related
Dec 30, 2013
When I run this query
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.
I am running MS Access 2010 on a Window 7 pc.
View 10 Replies
View Related
Aug 8, 2007
do you guys use a query as your data source in you form?
if not how do you build a form with mutilple tables with the same unique id.
View 1 Replies
View Related
Sep 21, 2007
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
Please help!!!
View 2 Replies
View Related
Jul 27, 2013
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...
View 1 Replies
View Related
Feb 23, 2005
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.
Thanks in Advance.
View 1 Replies
View Related
Jun 7, 2013
Can a query be used as a control source for a text box on a form?
=[QueryLibrary]![Kit] is the control source do I need to change anything else?
View 3 Replies
View Related
Aug 20, 2013
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?
View 3 Replies
View Related
Mar 25, 2015
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'")
View 3 Replies
View Related
Jul 27, 2005
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.
View 6 Replies
View Related
Mar 29, 2014
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.
View 2 Replies
View Related
Apr 27, 2013
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
View 14 Replies
View Related
Jul 28, 2013
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?
View 3 Replies
View Related
Dec 2, 2012
I am trying to add a query in an existing form. When I click on adding a field, I get an option to show "all tables" for not queries.
View 3 Replies
View Related
May 31, 2006
: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;
Thanks for you time! :)
View 1 Replies
View Related
Sep 19, 2005
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.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
View 14 Replies
View Related
Aug 17, 2005
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)”
Thanx
ami
View 2 Replies
View Related
Sep 23, 2005
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!
Super Thanx.
O.
View 5 Replies
View Related
Oct 2, 2005
Hi all
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?
Cheers
Rhys
View 4 Replies
View Related
Oct 4, 2005
Hi, I have a crosstab query I have set up, calculating the cost of a product per month
it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106
but instead it looks like
1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105
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
View 3 Replies
View Related
Jul 6, 2006
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?
View 1 Replies
View Related
Jan 25, 2007
I have a table with the following info:
Date/shift/name
1-1-07/1/tom
1-1/07/1/Bill
1-1-07/2/frank
1-1-07/2/Ben
1-1-07/3/Scott
1-1-07/3/Terry
1-2-07/1/tom
1-2/07/1/Bill
1-2-07/2/frank
1-2-07/2/Ben
1-2-07/3/Scott
1-2-07/3/Terry
1-3-07/1/tom
1-3/07/1/Bill
1-3-07/2/frank
1-3-07/2/Ben
1-3-07/3/Scott
1-3-07/3/Terry
What I want is the date to be displayed horizontally and the the names vertically with the shift as the intersection:
1-1-07 1-2-07 1-3-07
Tom 1 1 1
Bill 1 1 1
Frank 2 2 2
Ben 2 2 2
Scott 3 3 3
Terry 3 3 3
is this possible with a crosstab query or do I have to go about doing it some other way? If a crosstab query is possible how do you go about doing it?
Any help would be greatly appreciated.
View 3 Replies
View Related
May 30, 2007
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? :)
Something like this is what I need:
_______________Jan______________Feb_____________Ma r__________Apr
Power............1/3....200..............2/4...250...........3/7....225........4/5.....250
Lease............1/15...1200..........2/15...1200..........3/16..1200.......4/20...1200
Insurance.......1/16...175.............2/20...175...................0.....................0
.............................................. 2/26...350
Water/Gas.......1/6...150.............2/10...175...........3/3...150.........4/10...175
Thanks a lot...
View 10 Replies
View Related
Jun 29, 2007
Hi,
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?
Thanks!
View 1 Replies
View Related
