Solved

Crosstab Query

Posted on 2015-01-13
12
121 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 119

Expert Comment

by:Rey Obrero
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
 

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
Backup Your Microsoft Windows Server®

Backup 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.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now