Search results

Worked like a champ! Thank you! Why not allow it to be done directly from a crosstab? I find that if I have to go through multiple steps to get something done it usually means I'm doing it the hard way :)

Thank you, this makes me feel better. When I get into the actual maintenance of the data I'll look more into it, but for now I think I'll just stick to declaring the needed relationships in the queries themselves. Thanks again!!

In this post I learned a better way to organize my data using a crosstab query. Now I need to turn that crosstab query into a table so I can properly use it in other queries (I need the one-to-many the crosstab creates). However, the only way I can do that is to export the crosstab to Excel...

Why should you declare a relationship in relationship view when you can do it in the query? Let me explain first. I don't enter any data into my tables. All my tables are dumps from a main database and I'm just slicing-n-dicing the data into more usable pieces. How many customers or x type...

Holy cow! I completely stand humbled and corrected. I ran a crosstab just to see if I could use it in some way to help with the 22 queries I needed in the Union Query. I noticed other options in groupby other than count. I used 'First' and it gave me exactly what I needed. I apologize...

I tried a crosstab query, but I can't get the actual rep's name to be in the role column. Instead it only returns a count/sum/avg of the number of times a rep is in that role. Unless someone knows how to get the value portion of the crosstab query to return the needed text and not a count...

Thank you! It looks like Access 2007 on Vista is still in test, but I will happily keep them in mind. Thanks again!

Some of my queries get a bit long and I need to do a lot of cut/paste in the SQL view. To help with this I like to format the code to make it easier to read. However, when I come back the next day the formatting is lost and everything is all bunched-up. Does anyone know how to save the...

I have a table I'm having some trouble with and was hoping someone had already solved this issue. I receive a report, which sadly can't be changed, that has a list of customer IDs and the sales reps assigned to them as well as the reps role. The problem is there are duplicate customer IDs...

Sorry to bother the thread, but i just figured it out. My join was only on one of the rows in the crosstab. I went ahead and did a join for each category in the crosstab queries and it is working great! Live and learn :o (embarrassed smile)

I've been trying to create a crosstab query with multiple values with no luck. As was suggested in threads in this forum, as well as following Microsoft's suggestion, I first created two different crosstab queries; 1 for count and 1 for sum. I then made a third query and joined the first two...

That's actually very interesting. I like how you broke it up into two separate parts. It makes it much easier for me to figure out what is happening. Very cool. Thanks! I went ahead and bought two different books today which I hope will help. 1) Microsoft Access 2007 Data Analysis, by...

I've been learning that staying within the Design View in Access will only get you so far. I was having a problem with unique values in a crosstab query and received this great code from ByteMyzer (http://www.access-programmers.co.uk/forums/showthread.php?t=168901). When I looked at it in...

ByteMyzer, This worked!!! But I don't understand how :) I'll play around with it and see if I can figure it out. I'll need to duplicate it for later queries of course. Thank you again!! This has been killing me lately!

This is killing me. Here's my two queries - very simple. Query 1 gives me the unique count of customers because the 'group by' is working. It returns 644 unique 'large' customers in 'americas' in 2001. SELECT DISTINCTROW [Main Invoice Data].[EndUser OCN:], [Main Invoice Data].[Geo1:], [Main...

boblarson - I'm only linking the CustomerID fields. The unique query only returns the grouped CustomerIDs to get a unique count. Once I add other fields to the query, I get duplicate CustomerIDs (depending on the fields of course). ByteMyzer - I'll try your code. I don't get it right away...

I did that, but when I have to link it back to the main table to get the other information (Geo and Size), it gives me all the records which match the Customer ID by Geo and Size. The Crosstab just counts those and they're not unique. Thanks though!

I am pulling my hair out on this one. How do you get unique values in a crosstab query? I've tried using DISTINCT and DISTINCTROW to no avail. I always get record count, and not unique. In short, I have a table with about 30 fields ranging from CustomerID, CustomerSize, Geo, etc.....and of...

I'm not sure if I understand 'normalized' correctly. My data is pretty raw. I have all the sales (Invoice) data for one of our product lines from 2000-2008. The table has quite a few fields like Customer ID, Size, Quantity, $ Amount, etc.; about 15 fields in total. That's it, very straight...

I got a crosstab query to show what I was looking for. This reduced my queries from 70+ to 1. That is a great time saver. Now my only problem is getting it to show unique values. For the life of me I can't get anything other than count in the value section. Any ideas? How do you get a...