SQL help thread

[archcommus]

I'm probably going to have a number of SQL and database related questions over the course of the next few months, so I might as well ask them in one place on the trusty AT boards.

My question for tonight is this: Is there any way to pull a particular field from only records containing certain criteria throughout a table, and then summing those fields? I need to calculate a field (call it A) that depends on the total of another field (call it X) added up across only records that match a third field (call it Y) of the record I'm calculating A for.

Know what I mean?

Any help over the weekend would be appreciated!

 

[Armitage]

SELECT sum(X) as A FROM mytable WHERE Y=some_criteria;

Is that what you mean?

 

[archcommus]

Originally posted by: Armitage
SELECT sum(X) as A FROM mytable WHERE Y=some_criteria;

Is that what you mean?

Close. I do indeed need sum(X), however I need to USE that sum to calculate a value for A. So I can't really have a whole separate query just for this. And Y does equal some criteria, however that criteria is not constant, it must match the same field in the record that holds field A.

 

[beggerking]

SELECT sum(X) as B, someCalculation as A FROM mytable WHERE Y=[A];

 

[archcommus]

Originally posted by: beggerking
SELECT sum(X) as B, someCalculation as A FROM mytable WHERE Y=[A];

Thanks, this sounds like it's on the right track. However, I am doing this amongst another dozen or so SELECT and WHERE statements. So if I added to my WHERE statement what you have there, it'll limit all of my output to those records, instead of only limiting my sum calculation to those records.

 

[beggerking]

Originally posted by: archcommus

Originally posted by: beggerking
SELECT sum(X) as B, someCalculation as A FROM mytable WHERE Y=[A];

Thanks, this sounds like it's on the right track. However, I am doing this amongst another dozen or so SELECT and WHERE statements. So if I added to my WHERE statement what you have there, it'll limit all of my output to those records, instead of only limiting my sum calculation to those records.

Do you have the SQL statements? It'd be easier if you paste your complete code...

 

[archcommus]

Unfortunately no. It's saved at work. I'll try to give you an idea of what's going on.

SELECT B, C, D, E, F, G, A (need to calculate A)
FROM <various tables>
WHERE such and such.such such = such and such.such such
.......followed by various other joins
GROUP BY B, C, D, E, F
ORDER BY B

A needs to be calculated like this: F * (G / (SUM(G) across only select records))

Those select records need to have a C that matches the C in A's record.

I hope you can make sense of that!

 

[beggerking]

Originally posted by: archcommus
Unfortunately no. It's saved at work. I'll try to give you an idea of what's going on.

SELECT B, C, D, E, F, G, A (need to calculate A)
FROM <various tables>
WHERE such and such.such such = such and such.such such
.......followed by various other joins
GROUP BY B, C, D, E, F
ORDER BY B

A needs to be calculated like this: F * (G / (SUM(G) across only select records))

Those select records need to have a C that matches the C in A's record.

I hope you can make sense of that!

so add to WHERE [A]=[C]

 

[archcommus]

Originally posted by: beggerking

Originally posted by: archcommus
Unfortunately no. It's saved at work. I'll try to give you an idea of what's going on.

SELECT B, C, D, E, F, G, A (need to calculate A)
FROM <various tables>
WHERE such and such.such such = such and such.such such
.......followed by various other joins
GROUP BY B, C, D, E, F
ORDER BY B

A needs to be calculated like this: F * (G / (SUM(G) across only select records))

Those select records need to have a C that matches the C in A's record.

I hope you can make sense of that!

so add to WHERE [A]=[C]

I believe I tried that and it only output the records where C = C from record with A in it instead of only using those rows to calculate my sum.

Perhaps I need a second query.

 

[beggerking]

that may be the answer in this case..

 

[demon42]

Allright, I have a question for you guys:
Given a table name (from PHP) I'd like to find the names of it's columns. Is that possible?

The only answer I have is writing out for each table: if table name is this, then it's columns are these.

...but I'd prefer to be more abstract.

Thanks for your help!

 

[beggerking]

in asp
table.column.name

not sure in PHP... similar?

 

[MrChad]

Originally posted by: demon42
Allright, I have a question for you guys:
Given a table name (from PHP) I'd like to find the names of it's columns. Is that possible?

The only answer I have is writing out for each table: if table name is this, then it's columns are these.

...but I'd prefer to be more abstract.

Thanks for your help!

What's the underlying DBMS? MySQL? I know how to do this in Oracle and SQL Server.

 

[demon42]

Well, I heard about and tried the TOP keyword, but however the DBMS is set up, TOP wasn't recognized...
I ended up using a set of arrays and a function.

But good news, I have another question!

I have a table in which one column (product number) is auto-incremented when a new row is added.
When I delete rows, and then add more, the numbers start again where they left off!

Question: When deleting a row, is there some way to have the table fill itself in? My guess: not really.

 

[archcommus]

Another question for me, as well: How can I easily add an "all" option to a combo box? Right now I have simple query for the Row Source: SELECT Member_ID FROM dbo_Member; How can I add an "all" option so it lists all member IDs? I've read a few things from Google searches but none of their methods seem to be working.

 

[beggerking]

Originally posted by: demon42
Question: When deleting a row, is there some way to have the table fill itself in? My guess: not really.

not really possible since database records are static...

Another question for me, as well: How can I easily add an "all" option to a combo box? Right now I have simple query for the Row Source: SELECT Member_ID FROM dbo_Member; How can I add an "all" option so it lists all member IDs? I've read a few things from Google searches but none of their methods seem to be working.

are you talking about the use of select * from dbo_member ..?

 

[archcommus]

Originally posted by: beggerking

Originally posted by: demon42
Question: When deleting a row, is there some way to have the table fill itself in? My guess: not really.

not really possible since database records are static...

Another question for me, as well: How can I easily add an "all" option to a combo box? Right now I have simple query for the Row Source: SELECT Member_ID FROM dbo_Member; How can I add an "all" option so it lists all member IDs? I've read a few things from Google searches but none of their methods seem to be working.

are you talking about the use of select * from dbo_member ..?

Well, normally SELECT * FROM <table name> would produce all columns in a table, but I guess in this case it can be used to list all items from the list? I tried using that exact command in a number of ways with UNIONS and such but nothing worked. It added the * or the "all" option in the list, but that selection never pulled up any records.

 

[archcommus]

Here is my new Row Source code:

SELECT "*" AS Member_RecID, "(All Members)" AS Member_ID, 0 AS SortOrder FROM dbo_Member UNION SELECT Member_RecID, Member_ID, 1 FROM dbo_Member ORDER BY SortOrder, Member_ID;

If I choose any member ID from the list upon running the form, the report generates just fine. (All Members) does appear at the top of the list. If I choose it, however, I get this error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Here is my full SQL, important part bolded:

SELECT DISTINCT member.Last_Name, member.First_Name, pm_project.Project_ID, billing_method.Description, activity_class.Description, pm_project.Billing_Amount, Sum(time_entry.Hours_Invoiced) AS SumOfHours_Invoiced
FROM dbo_Billing_Log AS billing_log INNER JOIN ((((dbo_Time_Entry AS time_entry INNER JOIN dbo_PM_Project AS pm_project ON time_entry.PM_Project_RecID = pm_project.PM_Project_RecID) INNER JOIN dbo_Member AS member ON time_entry.Member_ID = member.Member_ID) INNER JOIN dbo_Activity_Class AS activity_class ON time_entry.Activity_Class_RecID = activity_class.Activity_Class_RecID) INNER JOIN dbo_PM_Billing_Method AS billing_method ON pm_project.PM_Billing_Method_ID = billing_method.PM_Billing_Method_ID) ON billing_log.Billing_Log_RecID = time_entry.Billing_Log_RecID
WHERE (((billing_log.Date_Created) Between [Forms]![Invoice Form]![Date_Begin] And [Forms]![Invoice Form]![Date_End]) AND ((time_entry.Member_RecID)=[Forms]![Invoice Form]![Member_RecID]))
GROUP BY member.Last_Name, member.First_Name, pm_project.Project_ID, billing_method.Description, activity_class.Description, pm_project.Billing_Amount
ORDER BY member.Last_Name;

Any ideas? Thanks.

 

[beggerking]

check your date columns.. datebegin and dateend.. make sure all of them are date type and none of the records are null.

 

[archcommus]

Hmm...well I can't change the database anyway since it's linked, but I can't find a way to check the data type of a column in a table in Access in the first place. There doesn't seem to be any sort of properties selection.

But since the report generates just fine for any individual member ID and just not for (All Members), I'm sure the dates aren't the problem here. I really don't understand why this wouldn't work. As you can see my code inserts an asterisk for the Member_RecID when you choose the (All Members) option. If you enter an asterisk for this directly into the query, it works fine, and shows all members. So there's no reason it shouldn't work this way through the form.

 

[beggerking]

right click on the table and select design view..you should be able to see table specifications..

Sorry I can't help you...the only time I've seen this kind of error message is when a ranged function hits invalid dates ( in your case, between ..and.. )
umm.. did you try selecting less columns?..

 

[statik213]

Originally posted by: demon42
Allright, I have a question for you guys:
Given a table name (from PHP) I'd like to find the names of it's columns. Is that possible?

The only answer I have is writing out for each table: if table name is this, then it's columns are these.

...but I'd prefer to be more abstract.

Thanks for your help!

try:

SHOW COLUMNS FROM <tblname>;

It's been a while but pretty sure it works in MySQL and I think it's standard SQL.

 

[Jeraden]

Originally posted by: demon42
Allright, I have a question for you guys:
Given a table name (from PHP) I'd like to find the names of it's columns. Is that possible?

The only answer I have is writing out for each table: if table name is this, then it's columns are these.

...but I'd prefer to be more abstract.

Thanks for your help!

Depending on what database you are talking about, there is probably a table/view out there that stores all this type of information. For instance, using Oracle if I wanted to see all columns from a specific table:
select * from sys.all_tab_columns where table_name = 'xxx';
I'd have to assume any database would have a similar table you could query off of.

 

[archcommus]

Originally posted by: beggerking
right click on the table and select design view..you should be able to see table specifications..

Sorry I can't help you...the only time I've seen this kind of error message is when a ranged function hits invalid dates ( in your case, between ..and.. )
umm.. did you try selecting less columns?..

I fixed it by changing SELECT "*" to SELECT "%", and by changing my WHERE clause to use LIKE instead of =. That seemed to work okay.

My next question is, is there any way to make a report in Access pull information for its fields from more than one query?