Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crosstab Query

Posted on 2015-01-13
12
Medium Priority
?
131 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 1500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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