i consider myself decent with SQL, but i'm an idiot when it comes to doing anything but the most basic things in excel...in this case, i have some data (hundreds of rows 25 columns wide) that i want to turn into some graphs, but i'm only concerned with data that matches certain criteria...let's say i have this:

+-----+-----+-----+-----+-----+
|  #  |  A  |  B  |  C  |  D  |
+-----+-----+-----+-----+-----+
|  1  |  1  |  9  |  9  |  4  |
+-----+-----+-----+-----+-----+
|  2  |  5  |  2  |  2  |  2  |
+-----+-----+-----+-----+-----+
|  3  |  1  |  9  |  5  |  2  |
+-----+-----+-----+-----+-----+
|  4  |  3  |  9  |  2  |  1  |
+-----+-----+-----+-----+-----+

1/16/2012 10:34:46 PM

how robust of a solution are you looking for? is this for a couple one-off graphs and then you'll be done, or do you need to produce new graphs on an ongoing basis? are the queries any more complex than the A=1 and B=9 example?

1/16/2012 10:53:27 PM

S-Q-Lite nuqqa

1/16/2012 10:59:18 PM

How about a pivot table/chart ?

1/17/2012 12:23:01 AM

Quote :

"S-Q-Lite nuqqa"

1/17/2012 2:00:15 AM

Quote :

"how robust of a solution are you looking for? is this for a couple one-off graphs and then you'll be done, or do you need to produce new graphs on an ongoing basis? are the queries any more complex than the A=1 and B=9 example?"

that's about as complex as it should be...maybe 3 requirements, but most likely just 2

it's just for creating some simple graphs

Quote :

"S-Q-Lite nuqqa"

google tells me there is a "sqlite for excel"...but would any of that be dependent on others' machines and whether they have it installed? i'd rather just keep this a pure excel experience

Quote :

"How about a pivot table/chart ?"

maybe...i didn't know what that was, but a quick google search tells me it might do what i want...i've never really used the wizards, just written out equations (and i was hoping that with only 2 restrictions, it might be a simple formula)

1/17/2012 7:45:39 AM

I first learned how to do pivot tables in my first job out of college...once I figured them out I used them every day from there-on

you can make one w/o using the wizard, on the right pane that pops up, drag the headings to the proper boxes and it will create it for you

1/17/2012 11:16:41 AM

^^What you're asking for is something only a true RDBMS would provide...

maBIE try importing it into Access? (It supports yet another variant of SQL.)

1/17/2012 7:37:46 PM

Quote :

"^^What you're asking for is something only a true RDBMS would provide... "

There's one table here.

Why can't you just use Excel's built-in filter functionality?

[Edited on January 17, 2012 at 8:10 PM. Reason : I assume you can't because it's more complicated than you've explained and you want to do it on]

[Edited on January 17, 2012 at 8:10 PM. Reason : the fly]

1/17/2012 8:09:47 PM

i just did something like this and used the built in excel filter. I was doing string values instead of integers as well

1/17/2012 8:59:40 PM

Can I thread hijack? I'm not sure if I need to use VBA for this, built in filters, or a pivot table.

I have a time tracking solution that generates an IIF file for quickbooks. The problem is that the paycode headers are set globally because most people don't have a need to keep track of salaried time. I was hoping that I could do one export and run a macro that checked the name field and if it matched a salaried employee, it would change another field from Hourly to Salary. Or from hourly-p-t-o to salary-p-t-o.

So in the below example, If column D's value = Jane J Doe and F value = Hourly then change to Salary. If F value = Hourly p-t-o change to Salary p-t-o.

Stuff like this kind of piques my interest, despite having hated Intro to Java and whatever other programming courses I barely put forth the effort to pass, so I would definitely take any nudge in the correct direction or a starting point. Not necessarily looking for anyone to write the script.

https://docs.google.com/spreadsheet/ccc?key=0AjGTO1ijgSqodFllODRqR2Y4WUsyeHNqaWpOSlFTUnc

Is an excel macro in VBA the right course? Or auto it, or what? =/

</thread.hijack>

2/15/2012 1:01:06 PM

vlookup

2/15/2012 7:04:10 PM

On Sheet 3 I have this

Jack Dick	Hourly
Jane Doe	Salary

Name	       Pay Type
Jane Doe	
Jack Dick	

2/15/2012 8:39:14 PM

oh shit that might work.
....

Well, it's a starting point I suppose. Ultimately I'm going to have to automate this because it's going to be different data every pay period.

[Edited on February 15, 2012 at 8:45 PM. Reason : ]

2/15/2012 8:43:05 PM

Are your employees and their pay types at least not changing?

2/15/2012 9:48:09 PM

So I'm the opposite. I'm slowly learning SQL so be easy on me.

Here's my problem. I have a certain number of tables and there's a field in each table that matches. I know how to create joins to get matching records but lets say I want records that match on 50% of the tables or 75% of the tables.

My first thought would be simple arithmetic on some type of field with 1's in it (say count field).

So if record has a count/total tables of =>50% I would capture all records that are in 50%+ of the tables.

Or am I going about that entirely wrong?

5/3/2012 6:37:52 PM

do all your tables have the exact same schema? if so, why aren't you putting all the data in a single table? can you give a little more detail about your tables and the columns in it? it'll be easier to ask you the right questions if i can use an actual example from your situation

5/4/2012 10:01:10 AM

Yeah that would probably be best. It's 11 digit ID numbers by month. So 9 months of IDs. Each month is a table with ID in one field and count in another.

Currently I've been doing joins to grab IDs and counts across multiple months. How would I go about creating a single table with ID in one field then 9 fields (each month) with counts?

5/4/2012 10:22:38 AM

Quote :

"How would I go about creating a single table with ID in one field then 9 fields (each month) with counts?"

Multiple joins in the same query...but it sounds like you're already doing that


If you could give us a sample query and an example of that ID it might help (me at least)

[Edited on May 4, 2012 at 10:39 AM. Reason : I don't think I like your DBA ]

5/4/2012 10:37:02 AM

Ok well I'm open to suggested to fix it.

5/4/2012 10:38:00 AM

+------+--------------+-------------+---------+
|  Id  |  Month       |  Code       |  Count  |
+------+--------------+-------------+---------+
|  1   |  2012-01-01  |  123456789  |  9      |
+------+--------------+-------------+---------+
|  2   |  2012-01-01  |  987654321  |  2      |
+------+--------------+-------------+---------+
|  3   |  2012-01-01  |  567891234  |  5      |
+------+--------------+-------------+---------+
|  4   |  2012-02-01  |  123456789  |  8      |
+------+--------------+-------------+---------+
|  5   |  2012-02-01  |  987654321  |  0      |
+------+--------------+-------------+---------+
|  6   |  2012-02-01  |  567891234  |  1      |
+------+--------------+-------------+---------+
|  7   |  2012-03-01  |  123456789  |  1      |
+------+--------------+-------------+---------+
|  8   |  2012-03-01  |  987654321  |  4      |
+------+--------------+-------------+---------+
|  9   |  2012-03-01  |  567891234  |  8      |
+------+--------------+-------------+---------+
|  10  |  2012-04-01  |  123456789  |  12     |
+------+--------------+-------------+---------+
|  11  |  2012-04-01  |  987654321  |  3      |
+------+--------------+-------------+---------+
|  12  |  2012-04-01  |  567891234  |  8      |
+------+--------------+-------------+---------+

declare @totalmonths int
select @totalmonths = count(distinct month) from data

select distinct code, (select count(*) * 1.0 from data d2 where d2.code = d.code) / @totalmonths
from data d

5/4/2012 11:03:56 AM

Ok simple query question.

I have 2 tables each with 2 fields. IDs and counts again. I want to union both tables together and if there are duplicates then add the count fields together.

I can't seem to figure out how to do this in one step...

5/14/2012 3:42:45 PM

Okay so I'm not sure how to get it in one query, but why are they in separate tables if they have the same columns? I'm assuming it has to do with monthly finance records or something?

In any case, I'd create a third table to dump your first two into -- where you don't have id as a primary key so you can have duplicates.

so you'd have table_a with values of your codes and their count

and table_b with their codes and counts

CREATE `table_c`

insert into table_c select * from table_a;
insert into table_c select * from table_b;

select id,SUM(count) from table_c group by id;


Not sure if this is what you are looking for, I'm sure someone else has a more elegant solution.

5/14/2012 7:07:16 PM

Another question...

My table has IDs and fields for 3 months which contain counts.

I'd like to run a query that returns IDs that have 4 or more counts in 2 or more months.

So ID 12345:
Jan: 1
Feb: 1
Mar: 1

Would not qualify.

ID 54321:
Jan: 2
Feb: 0
Mar: 4

Would qualify.

Make sense?

6/20/2012 10:43:54 AM

id 54321 only has 1 month with 4 or more counts. doesn't that mean it does not qualify?

6/20/2012 11:02:08 AM

No total is 4 or more. So ID 54321 would be a total of 6 so it would quality for 4+.

6/20/2012 11:13:17 AM

Figured it out using case statements. Essentially if jan > 0 + feb > 0 etc.

6/21/2012 11:27:51 AM