Crosstab Query

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..
Jass SainiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Rey Obrero (Capricorn1)Commented:
0
 
Jass SainiAuthor Commented:
Sorry..Due to my work restrictions..cannot play youtube anything
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
can you post a simplified version of your database here...?
0
 
Jass SainiAuthor Commented:
Here you go..please make the Crosstab query and make changes to BC1Chng1
Database13.accdb
0
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
When you are thrown into a project with minimal info and no knowledge of the Organization ....
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
ok,
I will look for your future questions...
;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.