Solved

Crosstab Query

Posted on 2015-01-13
12
125 Views
Last Modified: 2015-01-14
Hello,

Why is it that when you do inputs on the form...the crosstab query creates a new record ..that's fine.  But it brings the old value and new value into the new record..trying to understand crosstabs..
0
Comment
Question by:Jass Saini
  • 6
  • 5
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40546961
0
 

Author Comment

by:Jass Saini
ID: 40546991
Sorry..Due to my work restrictions..cannot play youtube anything
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40547518
Your question here is unclear,...
The crosstab query will only add a new "Row", ...if a new summary column is added.
For example, ...you have a crosstab query of "Sum of Sales",...summarized by "SaleYears"  across the columns, and "SalesPersons" in the rows,
In this case, ...a new salesperson "row" ("record" is not really the correct term when referring to a crosstab query) will only be added if you add a new salesperson...
In other words, ...if you have only three salespersons, ....and each of those three salespeople adds more sales, ..no new salesperson "rows" will be created (but the summaries will update)
Only when you add another salesperson will the number of (salesperson) rows increase...

But it brings the old value and new value into the new record
I am not really sure what this means, ...as again, a crosstab will typically only summarize data, individual vales will typically not display at all, only the corresponding "summaries" will be displayed.
So you may have to provide a sample database that exhibits this behavior...

JeffCoachman
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Jass Saini
ID: 40547595
Hello jefferey,

that's what I thought...My boss wants the users data input to be placed under a Communication header when it is a input ..and that field is different than the Communication field.  I can see where it does summarizes items and does the sum caluculation as well.  I am at my wists end....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40547752
can you post a simplified version of your database here...?
0
 

Author Comment

by:Jass Saini
ID: 40547803
Here you go..please make the Crosstab query and make changes to BC1Chng1
Database13.accdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40548061
Notes
1. Your "Final_Table", ...does not have a typical "normalized" structure, ...this may be why your supervisor changed it...

2. What you are calling a crosstab "query", is really just a table. (a normalized version of "Final_Table", ...perhaps?)
A True crosstab query, ...is an actual query.
So when you refer to your table there as a query,...it creates confusion.
It looks like it might have been a true crosstab query at some point, ...but it was exported to Excel, then imported back into Access as a table (as evidenced by the numeric datatypes defined as "Double")
...please clarify...

3. There is a field in the new table with named SumOfTotal, ...This, to me, should be calculated in a query, ...and not "stored" in a table., ...please clarify...

4. You may wish to adapt a more standard naming convention like Leszynski:
(http://en.wikipedia.org/wiki/Leszynski_naming_convention)
or RVBA:
http://www.xoc.net/standards/rvbanc.asp
The field names you have there are non-standard and will likely cause issues down the line...

5. You appear to have numeric (or currency) fields like: Reimbursement, Postage, ...etc, ....yhat contain numeric data,  ...but are defined as text fields...?

6. You may wish to give this table a more descriptive name (tblOrganizations, ...perhaps)

7. You seem to need a "Fund" table.

At this point, (before you go much further) ...you should really sit down with your supervisor and a dedicated database professional, to see if this design is optimum for what you are trying to achieve with this system.

JeffCoachman
0
 

Author Comment

by:Jass Saini
ID: 40549139
I understand Jeff....But there is no dedicated database professional here to help.  I was thrown in with this database already started by someone else.  I have no guidance or professional to help.  Thus you guys.

Yes on #2 ---I made a crosstab query and export and then import to a table.  I will redo the crosstab query and see...and do the calculations there.

You are right about sitting down with my boss.  But I feel like she doesn't know what she wants and I don't know enough about access to tell her.  She doesn't understand about records and record keeping.

Thanks for your help.
0
 

Author Closing Comment

by:Jass Saini
ID: 40549145
When you are thrown into a project with minimal info and no knowledge of the Organization ....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40549254
OK,

I understand,...
Its just that "from a distance", ...there is not a lot we can do to help with design issues without a full understanding of all aspects.
I will redo the crosstab query and see...and do the calculations there.
It is still not clear if you even need a crosstab query, ...If your tables were normalized properly, ...you could do your Sum calculations in a standard query, ...Also, ... remember that you cannot do any data entry into a crosstab query.
(It only summarizes data.)

You really need to post a question explaining the full scope of this system, .and ask for very basic design advice.
Don't tell us what you have and what you want to do, (because it is still not clear if your design here is optimal)

Just state, in broad terms, what you need to accomplish, then experts can suggest designs that you can test *before* committing to a final design.

JeffCoachman
0
 

Author Comment

by:Jass Saini
ID: 40549443
Jeff,

No I understand too!!  Like I said I am still new at this too....But I am learning so much as I go along.  A

Management wants to see the line Items as a Column heading....

So what I am trying to accomplish is for every "Line Item" the Total Allotment will change based off the user inputs.  I need to save those inputs, Type of Change and the user remarks.  everything is based off the Main form..Analyst and thier Program types.

I have four forms for each Change---Kind of like Accounting...keeping a rolling tab.  This is what I am trying to accomplish.  But since I am new it's hard to know which direction to go.  I thought my boss was an expert at Access?  

Thank You for your understanding!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40549468
ok,
I will look for your future questions...
;-)

Jeff
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question