Solved

Crosstab Query

Posted on 2015-01-13
12
128 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

696 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