Queries :: Crosstab Query - Formatting User Defined Columns

Jul 24, 2013

I am making a classic sales over time crosstab query.

Rows: Customers
Columns: Sale months

Sales date is defined by the ETD of the order.

However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).

August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013

Is it possible to format the columns this way?

View Replies


ADVERTISEMENT

CrossTab With Defined Columns

Dec 28, 2005

:D I am trying to create a CrossTab query from a table of expenses:

tblExpenses:
PropertyID
ExpenseType
Amount

There are many kinds of Expense Types. I want to use ExpenseType as the column header, BUT I want there to be 3 categories "Rent Expense," "Taxes," and "Other."

HOW do I group all expense types <> "Rent Expense" and "Taxes" in the third column?

I can get a comprehensive query with many columns, or set criteria to get just "Rent Expense" and "Taxes" columns, but I cannot get the 3 columns.

How can I do this? Thanks for your help!!

:confused:

View 1 Replies View Related

Queries :: Can Retrieve Value Of User-defined Type In A Query

Aug 19, 2013

Is there a way to retrieve the value of a user-defined type in a query?

Here's the type:

Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type

I have a function with the following excerpt:

Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function

In my query I expected to be able to put the following:

Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount

However, Access didn't like the period in .Rank or .ClassCount.

Should I just write two different function to get 'rank' and 'classcount'?

View 2 Replies View Related

Queries :: Conditional Formatting On Column Not Listed In Crosstab Query

Mar 26, 2013

I have a simple crosstab query:

FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).

These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).

This is the report the crosstab query generates:

What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.

Can I do this?

View 3 Replies View Related

Queries :: Formatting Columns In A Query With Totals

Oct 2, 2013

The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).

I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.

View 1 Replies View Related

Queries :: Sort Crosstab Query Columns And Generate Report

Jul 28, 2015

This is a query, report and vba question. I'm using Ms Access 2007.

TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate

I created a select query to join the info that I need.

SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))

I have 2 crosstab queries.

CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))

I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).

SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)

It gives me this:

However, I want it like this:

Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.

View 8 Replies View Related

Queries :: Taking User Input In CrossTab Query

Jul 29, 2013

I have a cross tab query that takes input from the user. The code is as under:

PARAMETERS [START_MONTH] DateTime, [END_MONTH] DateTime;
TRANSFORM Round(Sum(([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 ((PRODUCTION.MONTH) Between Format([START_MONTH],"dd-mm-yyyy") And Format([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;

I am facing 3 problems with the above code:

1. Access prompts to enter the start month and end month even when I click the save button.
2. When I run the query it prompts to enter the start month and end month twice.
3. I have generated a report based on the above query. When I run the report it keeps on prompting for start month and end month and does not give the final result.

Is it a common problem with cross tab queries?

View 5 Replies View Related

Queries :: Cannot Get User Input Box To Open On Crosstab Query

May 26, 2015

I have a crosstab query. users need to run the query for any week of a year. It is always a YTD query. I have used this process on many select queries. Will a crosstab query not support this simple method? See attached screen shots.

View 2 Replies View Related

Modules & VBA :: Compile Error - User-defined Type Not Defined

Apr 20, 2015

I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.

Function OpenAutoCount()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

[code]...

View 3 Replies View Related

General :: ADODB Connection - User Defined Type Not Defined

Feb 10, 2014

I have a line of code in an old program:

Dim CN as ADODB Connection

This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?

View 1 Replies View Related

User Defined Date In Queries

Apr 17, 2008

Hello all, im new here so hi to everyone.

Im stuck! I am pretty much a novice when it comes to access and have been given the task or creating some reports to stop us doing it manually.

Within a query, I have a date column which we would currently enter the following into the criteria:

Between #01/01/2008# And #31/01/2008#

for the month of January.

What i want to be able to do (and i know is possible as ive done it in the past but cant remember how) is have a pop up when the query is ran to enter the Start date and End date. I remember it being something to do with square brackets but cant get it to work.

Help please! lol

View 14 Replies View Related

Queries :: Query That Uses Different Columns Based Upon User Input

Sep 10, 2013

I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:

Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3

Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.

View 1 Replies View Related

Queries :: User Defined Criteria On Percent Field?

May 7, 2013

I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.

One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:

Code:

SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc
FROM tbl_OSRImport
WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));

This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:

In the Query, the expression is: VarPerc: [OverShort]/[NetSales]

When the Query runs, the full numerical result is: -4.27103159497526E-02

Which visually is output as: -4.72%

Mathmatically (on a calculator using the same values) the equation is as follows:

-11.22 / 262.70 = -0.04271032

I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.

View 5 Replies View Related

Queries :: User Defined Criteria For Number Field

May 28, 2015

Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).

This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).

View 3 Replies View Related

Error - User Defined Type Not Defined

Nov 9, 2005

Hi,

On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.

How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?

Thank you in advanced programming wizards. Kind regards, Adam.

View 14 Replies View Related

User Defined Parameter Queries / Forms Major Problems !!

Oct 25, 2006

Good Morning

Looking for the user to select a product name from a drop down box (not type in) when running query / report which will return details on only that product rather than all others in the query.

I have got major problems with the custom dialog boxes -- have got a table of product names, a query called current stock ( active products) . I can easily get a standard dialog box by putting [product code] in brackets in the query and this is not a problem. However in getting a custom dialog box I have had lots of problems.

Created a form called form999 which has product name field as a combo box and this has been named master1 . Clicking the form on it's own brings up the dialog box correctly and the drop down box selects the current products which is should do.
However linking it to the query I have put [Forms]![form999]![master1] in the query and it will not link unfortunately and is driving me mad.
Upon running the query it simply brings up the standard dialog box with [Forms]![form999]![master1] as the "enter parameter value. Understand code is required to get the OK and cancel macros working etc but when I click the query it should bring up the custom box which is the first stage and it is not ?? Any assistance would be greatly received.

Are there any sample DBs which have a field or related parameter as a customer dialog box -- have looked for examples without success.

many thanks and regards
Ian Watson Yarm UK

View 2 Replies View Related

Queries :: Show User ID When There Are No Errors During Defined Time Frame

Apr 16, 2014

I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame.

View 9 Replies View Related

User Defined Query

Sep 2, 2004

I have a combo box that the user can select a value and the form will display that record on the form. This all works fine, but if the user dosn't enter anything in the combo box, it does not work. Is there a way to have it work if the combo box is left empty?
Thanks in advance - John

View 1 Replies View Related

Queries :: Crosstab Queries Columns Heading Limitation

Apr 30, 2015

I was wondering how to do a crosstab query and have to column headings

I need the Organization Number and the Org name..so something like this

4005 4010

Office of HQ Office of Accounting

Is this possible?

View 2 Replies View Related

User Defined Query/ Report

Feb 7, 2008

Hello,

Is there any way of creating a user defined query/ report based on fields in a table? Basically we want a report to be generated based on whatever fields the user enters. So, say we have 30 different fields in a table, the user gets to click on a button that gives them the option to create a report that includes: field1, field2, field3 and where field10 = Blue.
Is this possible to do?

View 1 Replies View Related

User Defined Date Query

Apr 16, 2008

I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.

Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?

Look forward to your feedback - thanks!

View 2 Replies View Related

Queries :: Crosstab To Convert Rows To Columns

Sep 16, 2014

I have data like:

Code:

SubjectID VisitID VisitNum VisitDate VisitDetail Rspns
1 5 1 2/10/2001 Fever_1 Yes
1 5 1 2/10/2001 Age_1 12
2 7 1 5/08/2010 Fever_1 No
2 7 1 5/08/2010 Age_1 18
2 8 2 9/30/2010 Fever_2 Yes
2 8 2 9/30/2010 Age_2 18
etc.

I need to convert it to a non-normalized wide format, like this:

Code:
SubjectID VisitDate_1 Fever_1 Age_1 VisitDate_2 Fever_2 Age_2
1 2/10/2001 Yes 12
2 5/08/2010 No 18 9/30/2010 Yes 18
etc.

If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:

Code:
TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;

View 1 Replies View Related

User-defined Type Not Defined

Oct 15, 2004

Dim XL As Excel.Application

When I try to run this specific line of code an error occurs. It says:
"User-defined type not defined"

May I know how to solve this problem?
Thanks a lot

View 1 Replies View Related

User-defined Type Not Defined

Sep 27, 2005

I've put in this in a module many times and this is the first time I have gotten an error.

Dim Conn As ADODB.Connection

When I run the app I am getting a compile error saying "User-defined type not defined".

Anyone have any ideas?

Thanks,

B

View 3 Replies View Related

Queries :: Building Crosstab For Logic To Evaluate 2 Columns

Jul 17, 2013

Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.

Objective:
If
A Fed has (1 or many) date submitted
AND
A St has (1 or many) date submitted
Condition is TRUE

How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.

View 1 Replies View Related

Queries :: Include Date Criteria In User Defined Function That Calculate End Of Current Month

Jul 22, 2014

I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().

The field on which this function is to enter as a criteria is another calculated date function called Due.

When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved