Database

Corporal Agarn6-Dec-12 9:30

Corporal Agarn

6-Dec-12 9:30

Microsoft does not know that Excel can now hold more rows, even when you tell it to use the newer format.

Mycroft Holmes6-Dec-12 16:57

6-Dec-12 16:57

65k rows is NOT a report, it is a data dump. You are using the wrong tool for the job, you should be exporting the data as csv either from the database or your client application NOT a reporting tool!

Never underestimate the power of human stupidity
RAH

berba6-Dec-12 18:06

berba

6-Dec-12 18:06

The report is loading fine on the asp.net webpage via the report viewer control.

It's just when exporting to excel the customer is getting an error. In PDF it is fine

Mycroft Holmes6-Dec-12 21:43

Re: SQL Group By Question

6-Dec-12 21:43

berba wrote:
It's just when exporting to excel the customer is getting an error

Which means it is the WRONG tool for the job. The tool has a limitation, as pointed out by David and djj.

Never underestimate the power of human stupidity
RAH

SQL Group By Question

loyal ginger6-Dec-12 5:27

loyal ginger

6-Dec-12 5:27

I am trying to do this in Microsoft Access. I seem to be unable to construct a 'simple' query. To illustrate, here is a sample table:

Field1	Field2	Amount 
A	New	10
D	Old	5
G	Old	1
H	Old	15
H	New	55
X	Old	20
Z	Old	100
Z	New	20

I need to group by Field1 and only return the Field2 data for the record that has the maximum number in the Amount field. I tried to use the 'First' function but Microsoft Access sort the records internally so it does not return the right value even if I pre-sort the records with a sub-query. Do you have any advice? The result should look like the table below.

Desired Result:

Field1	Field2	Max(Amount)
A	New	10
D	Old	5
G	Old	1
H	New	55
X	Old	20
Z	Old	100

I have trouble getting the value of Field2 in the record containing to the maximum value of 'Amount.'

Any suggestions? Thanks in advance for your time!

modified 6-Dec-12 11:51am.

Blue_Boy6-Dec-12 10:11

Blue_Boy

6-Dec-12 10:11

You can try this query

SQL

SELECT mt.Field1,
       (
           SELECT TOP 1 mt2.Field2
           FROM   myTable mt2
           WHERE  mt2.Field1 = mt.Field1
           ORDER BY
                  mt2.Amount DESC
       ) AS Field2,
       MAX(mt.Amount)
FROM   myTable mt
GROUP BY
       mt.Field1

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: SQL Group By Question

loyal ginger31-Dec-12 5:48

loyal ginger

31-Dec-12 5:48

Thanks for your time!

Issues installing ODP.NETx64

cpp_prgmer6-Dec-12 4:18

cpp_prgmer

6-Dec-12 4:18

I just got a new 64bit machine and I'm migrating my code from my old 32bit machine. I wanted to install Oracle Data Providor for .NET, the 64bit one and I selected 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64 bit it had a note:
"Important: The 32-bit Oracle Developer Tools for Visual Studio download is required for Entity Framework design-time features".
So I installed the 32 bit Oracle Data Provider for .NET which is ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) with the hope that I could do the 64 bit installation on top of that.
Now the 64bit installation does not go through and I am not able to uninstall the 32 bit installation and I have no clue how to get out of this mess.

Sorry this is my first time installing as well as posting, experts please guide me.

Eddy Vluggen6-Dec-12 14:37

Eddy Vluggen

6-Dec-12 14:37

"Virtual Machine"

Don't use your primary development-machine as a testing-ground; use a virtual machine. I know it's slower, but it's also a lot safer.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Peter_in_27806-Dec-12 15:12

Peter_in_2780

6-Dec-12 15:12

Sounds like your advice comes a bit late...

Cheers,
Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

Mycroft Holmes6-Dec-12 16:52

6-Dec-12 16:52

I imagine he is trying to set up his primary development machine, not just testing a new environment.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes6-Dec-12 16:53

SQL Query - append results

6-Dec-12 16:53

Investigate wether you have a system restore point prior to the installation of the driver!

Never underestimate the power of human stupidity
RAH

AndreFratelli5-Dec-12 6:43

AndreFratelli

5-Dec-12 6:43

Hi everyone,

I'm having trouble with this query in SQL (homework!).

I have two tables: customer and staff.

== Customer (the relevant fields) ==
customer_id
first_name
last_name

== Staff ==
staff_id
first_name
last_name

The question is: "select the first and last names of all customers and staff".

Can anybody help on how to do that? I don't know how to "append" the results of the two queries, and googling I can only find about INSERT ... SELECT, which is not what I'm looking for =\

Best regards to all.

Fratelli

Re: SQL Query - append results

Chris Meech5-Dec-12 6:55

Chris Meech

5-Dec-12 6:55

Perhaps the keyword "UNION" may help you. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: SQL Query - append results

AndreFratelli5-Dec-12 7:08

AndreFratelli

5-Dec-12 7:08

It does indeed ! Big Grin | :-D

Thank you

Fratelli

SQL 2008 Data Base Table Design suggestion

VishwaKL4-Dec-12 23:36

VishwaKL

4-Dec-12 23:36

Hi Guys,

I have a problem to create data base.

My requirement is like

there are 4 different sites are there and every site has a common checklist to be checked and sites also have extra check list to checked with common check list, for this how i should create data base table.?

Thanks in advance
Vishwa

Simon_Whale4-Dec-12 23:43

Simon_Whale

4-Dec-12 23:43

From you brief description;

I am assuming that you are using a central database.

I see that you would need to create a

site table (used to store the sites that will use the database)
Checklist Template (list of things to check off)
Checklist Completed table (a join table that links the site with the completed items from the template table)

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

VishwaKL4-Dec-12 23:47

VishwaKL

4-Dec-12 23:47

Thanks Simon

that extra check list are not common to all sites so. how can i relate those points

like which is required which is not for particular Site?

Simon_Whale4-Dec-12 23:51

Simon_Whale

4-Dec-12 23:51

well in the check list completed table (more than likely not a good name) you could record the todo item and a datetime completed? and the site that it is linked to.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

[HELP] how to using array in my case? (Query & PHP)

PIEBALDconsult5-Dec-12 3:56

PIEBALDconsult

5-Dec-12 3:56

Your description isn't clear enough, so here's a design that could be used for some interpretations of what you wrote.

0) There are several sites.
1) A check list has several check list items.
2) Each site has a check list with a subset of all available check list items.
3) Each check list item may be on several sites' check lists.

This is a classic many-to-many relationship and the typical implementation is to have (at least) three tables:

Site: ID, Name, etc.
Item: ID, Name, etc.
SiteList: SiteID, ItemID (usually no other data here, but maybe in some cases)

Another schema would allow for defining lists and assigning them to sites:

Site: ID, Name, etc.
Item: ID, Name, etc.
List: ID, Name, etc.
ListItem: ListID, ItemID (usually no other data here, but maybe in some cases)
SiteList: SiteID, ListID (usually no other data here, but maybe in some cases)

This second way may be approriate for your needs, you can define a common list and custom lists and assign multiple lists to the sites. Getting a complete list of all items for a site is simple as well.

jimmy.suharry4-Dec-12 20:14

jimmy.suharry

4-Dec-12 20:14

I've some data, there are

RealData Balance 	 Sort by Desc
A  684 	 345 		 A  684 
B  60 	 -   		 E  308 
C  -   	 -   		 J  302 
D  -   	 -   		 I  273 
E  308 	 - 		 O  178 
F  -   	 -   		 H  140 
G  11 	 -   		 B  60 
H  140 	 - 		 N  27 
I  273 	 - 		 Q  22 
J  302 	 -   		 R  20 
K  -   	 -   		 P  14 
L  -   	 -   		 G  11 
M  -   	 -   		 C  -   
N  27 	 -   		 D  -   
O  178 	 38 		 F  -   
P  14 	 -   		 K  -   
Q  22 	 -   		 L  -   
R  20 	 4   		 M  -   

Description
Proc = Process
Res = Result
Sort by Desc

Get 8 row data 														
Proc 1  Res 1 	Proc 2  Res 2 	Proc 3 	 Res 3 	 Proc 4  Res 4 	 Finish for 8 row data
A  684  657 	A  657  635 	A  635 	 621 	 A  621  610 	 A  610  	
E  308  281 	E  281  259 	E  259 	 245 	 E  245  234 	 E  234 	
J  302  275 	J  275  253 	J  253 	 239 	 J  239  228 	 J  228 	
I  273  246 	I  246  224 	I  224 	 210 	 I  210  199 	 I  199 	
O  178  151 	O  151  129 	O  129 	 115 	 O  115  104 	 O  104 	
H  140  113 	H  113  91 	H  91 	 77 	 H  77 	 66 	 H  66 	
B  60 	 33 	B  33 	11 	R  20 	 6 	 B  11 	 -   	 R  6 	
N  27 	 -   	Q  22 	-   	P  14 	 -   	 G  11 	 -   	 B  -   	
Q  22 	 22 	R  20 	20 	B  11 	 11 	 R  6 	 6 	 G  -   	
R  20 	 20 	P  14 	14 	G  11 	 11 	 P  -    -   	 P  -   	
P  14 	 14 	G  11 	11 	Q  -   	 -   	 Q  -    -   	 Q  -   	
G  11 	 11 	N  -    -   	N  -   	 -   	 N  -    -   	 N  -   	
C  -    -   	C  -    -   	C  -   	 -   	 C  -    -   	 C  -   	
D  -    -   	D  -    -   	D  -   	 -   	 D  -    -   	 D  -   	
F  -    -   	F  -    -   	F  -   	 -   	 F  -    -   	 F  -   	
K  -    -   	K  -    -   	K  -   	 -   	 K  -    -   	 K  -   	
L  -    -   	L  -    -   	L  -   	 -   	 L  -    -   	 L  -   	
M  -    -   	M  -    -   	M  -   	 -   	 M  -    -   	 M  -   	

Result 	 1 	 27 
Result 	 2 	 22 
Result 	 3 	 14 
Result 	 4 	 11 

Total 1		 74 (For 8 row data)



Get 4 row data 														
Proc 1 	 Res 1 	 Proc 2  Res 2 	 Proc 3  Res 3 	 Finish for 4 row data
A  610 	 411 	 A  411  376 	 A  376  347 	 A  347  345 
E  234 	 35 	 O  104  69 	 O  69 	 40 	 O  40 	 38 
J  228 	 29 	 H  66 	 31 	 H  31 	 2 	 R  6 	 4 
I  199 	 -   	 E  35 	 -   	 J  29 	 -   	 H  2 	 -   
O  104 	 104 	 J  29 	 29 	 R  6 	 6 	 J  -    -   
H  66 	 66 	 R  6 	 6 	 E  -    -   	 E  -    -   
R  6 	 6 	 I  -    -   	 I  -    -   	 I  -    -   
B  -   	 -   	 B  -    -   	 B  -    -   	 B  -    -   
G  -   	 -   	 G  -    -   	 G  -    -   	 G  -    -   
P  -   	 -   	 P  -    -   	 P  -    -   	 P  -    -   
Q  -   	 -   	 Q  -    -   	 Q  -    -   	 Q  -    -   
N  -   	 -   	 N  -    -   	 N  -    -   	 N  -    -   
C  -   	 -   	 C  -    -   	 C  -    -   	 C  -    -   
D  -   	 -   	 D  -    -   	 D  -    -   	 D  -    -   
F  -   	 -   	 F  -    -   	 F  -    -   	 F  -    -   
K  -   	 -   	 K  -    -   	 K  -    -   	 K  -    -   
L  -   	 -   	 L  -    -   	 L  -    -   	 L  -    -   
M  -   	 -   	 M  -    -   	 M  -    -   	 M  -    -   

Result 	 1 	 199 
Result 	 2 	 35 
Result 	 3 	 29 
Result 	 4 	 2 
		
Total 2 	 265 (For 4 row data)

How to get this data using Query or Function? And how to using in PHP?
Please help!

Thank you.
Jimmy

modified 5-Dec-12 2:24am.

Re: [HELP] how to using array in my case? (Query & PHP)

Mycroft Holmes4-Dec-12 20:38