Re: Create Table - Database Discussion Boards

First Prev Next

Re: How to filter a number

matjame12-Nov-07 22:48

matjame

12-Nov-07 22:48

Hi Adeel.

My Birthdate is a string. Same as the IDNumber.

Thanx in advance.

kagiso

Re: How to filter a number

Adeel Chaudhry12-Nov-07 22:57

Adeel Chaudhry

12-Nov-07 22:57

Hope this is what you require:

http://www.techonthenet.com/access/functions/string/mid.php[^]

Regards,

Adeel

Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.

Re: How to filter a number

matjame12-Nov-07 23:09

matjame

12-Nov-07 23:09

Thank you very much.It helped a lot

kagiso

Create Table

prithaa12-Nov-07 18:56

prithaa

12-Nov-07 18:56

Hello,
If I want a table with decimal then what is te syntax I should use to create the table.
I have doubt about the data type to be used "decimal(10,2)"

Is the following right?
Create Table Sales (Amt decimal(10,2))

Thanks
Prithaa

Re: Create Table

pmarfleet12-Nov-07 19:42

pmarfleet

12-Nov-07 19:42

Have you tried it?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Create Table

prithaa12-Nov-07 19:54

prithaa

12-Nov-07 19:54

Hello,

Yes I have tried it and it gives me field definition error.

I think the problem lies in declaring right kind of data type . decimal(10,2) or numeric(10,2) doesn't work.

Pritha

Re: Create Table

pmarfleet12-Nov-07 21:36

pmarfleet

12-Nov-07 21:36

The syntax you gave in your original post works for me. There must be another error in your code. Have you posted all your code?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Create Table

Pete O'Hanlon12-Nov-07 22:38

Pete O'Hanlon

12-Nov-07 22:38

When posting a question like this, you may want to include information such as the database you are using. It could be that you we try something on SQL Server that works, and you think we're looking at Access.

Deja View - the feeling that you've seen this post before.

Re: Create Table

venkat vuppala12-Nov-07 23:25

venkat vuppala

12-Nov-07 23:25

try this

create table<table name>(<br />
<field1> <datatype>,<br />
<field2> <datatype>,<br />
<field3> <datatype>,<br />
..................,<br />
..................,<br />
..................,<br />
<fieldn> <datatype><br />
)

Re: Create Table

prithaa12-Nov-07 23:53

prithaa

12-Nov-07 23:53

Hello,

Thanks for your replies

I am using this in Access.The statement
Create Table Sales (Amt decimal(10,2)) is written in Access.

I get the error Syntax error in field definition.
Prithaa

Server Explorer not visible.

mgr_2k712-Nov-07 16:48

mgr_2k7

12-Nov-07 16:48

Message: Clicking on the following menu items does nothing.

View -> Server Explorer

Tools -> Connect to database

Tools -> Connect to server

Please advise.

Frown | :(

Manish Ganguly

Re: Server Explorer not visible.

joemonvarghese12-Nov-07 17:26

joemonvarghese

12-Nov-07 17:26

hi
presess Ctrl+Alt+S

try it..

Re: Server Explorer not visible.

mgr_2k712-Nov-07 17:29

mgr_2k7

12-Nov-07 17:29

Hi joe,

tried it. no luck.
What is it suppose to do? Frown | :(

Manish

Re: Server Explorer not visible.

mgr_2k712-Nov-07 23:44

mgr_2k7

12-Nov-07 23:44

FINALLY GOT IT....

HAD TO TYPE THE FOLLOWING IN THE COMMAND PROMPT

devenv /resetskippkgs

Smile | :)

Re: Server Explorer not visible.

venkat vuppala12-Nov-07 23:18

venkat vuppala

12-Nov-07 23:18

hi do u try this key (ctrl+alt+L)

Database problem

kingletas12-Nov-07 11:28

kingletas

12-Nov-07 11:28

hey all,

I think this is more problems with the computers i am working on, i don;t have administrator rights, rather than the database itself...

I have a database file that i would to have as many applications to access it as possible, but the problem is since i don't have the rights the sql server reportst that under default configurations remote connections cannot be established. However when i add it to each individual application is added it and works fine.

My question is, is there a way i can have a central database as i would do if i could have sql server running?

I am using C# as the programming language.

Thanks all,

Best regards...

Luis E Tineo S

Re: Database problem

Michael Sync12-Nov-07 14:29

Michael Sync

12-Nov-07 14:29

You mean, you wanna host the SQL database locally, right?

If you are using SQL 2000, you can install MSDE on your Windows 2000, XP. or you can install SQL server if your OS is the server-type (e.g. Windows 2000 Advanced Server or Windows 2003)

If you are using SQL 2005, you can install SQL 2005 Express (Advanced setup that includes the Management Studio) on your Windows XP or Vista.

Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)

"Please vote to let me (and others) know if this answer helped you or not. A 5 vote tells people that your question has been answered successfully and that I've pitched it at just the right level. Thanks."

the list of customers who own all stocks

Michael Sync12-Nov-07 7:01

Michael Sync

12-Nov-07 7:01

Let's say there are three tables (Customers, Stocks, CustomersStocks) in my database.

Customers
- CustomerID
- CustomerName

Stocks
- StockID
- Stockname

CustomersStocks
- CustomerID
- StockID

My question is ~ How can I get the list of customers who own all stocks?

For example, I have the data as below ~

Customers
- 1 | Michael
- 2 | Julia
- 3 | Lilian

Stocks
- 1 | Apple
- 2 | Orange

CustomersStocks
- 1 | 1
- 2 | 1
- 3 | 1
- 3 | 2
- 1 | 1

I wanna get the list of customers who own all stocks. In my example, the result will be "Lilian" . (should NOT include "Michael" as he doesn't own all stocks and he just own one stock twice.)

I wrote this SQL query below ~

<br />
SELECT DISTINCT dbo.Customers.CustomerName<br />
FROM         dbo.CustomersStocks INNER JOIN<br />
                      dbo.Customers ON dbo.CustomersStocks.CustomerId = dbo.Customers.CustomerID<br />
WHERE STOCKid <br />
GROUP BY dbo.Customers.CustomerName, dbo.CustomersStocks.StockID, dbo.CustomersStocks.CustomerId<br />
HAVING      (dbo.CustomersStocks.StockID =<br />
                          (SELECT     COUNT(*) AS CountOfStock<br />
                            FROM          dbo.Stocks))

It works okay.. but the customer who own a stock twice (e.g. "Michael" in my case) included in the result.. This is the problem..

So, How can I get the list of customers who own all stocks?

Thanks in advance.

Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)

Re: the list of customers who own all stocks

Chris Meech12-Nov-07 10:16

Chris Meech

12-Nov-07 10:16

You probably need to create a view of CustomersStocks that contains only a single entry for each customer instead of the multiple entries. Perhaps a select distinct might do it.

create or replace view uniquecustomersstocks as
select distinct customerid, stockid from customersstocks
;

Chris Meech
I am Canadian. [heard in a local bar]
Donate to help Conquer Cancer[^]

Re: the list of customers who own all stocks

Michael Sync12-Nov-07 14:19

Michael Sync

12-Nov-07 14:19

Actually, I don't want to create a view to get this result.. I think that it is possible to get the result with one line (maybe..big line) of SQL

Re: the list of customers who own all stocks

pmarfleet12-Nov-07 10:19

pmarfleet

12-Nov-07 10:19

One way of doing this would be:

1) CROSS JOIN Customers with Stocks to get every possible combination of customers and stocks.

2) Do a LEFT JOIN between 1) and CustomersStocks. Get a DISTINCT list of the Customer IDs where there is no matching record on the RHS of the join. These are the customers who have not bought at least 1 of the available stocks.

3) Return the records from Customers where the Customer ID is NOT IN the set of records returned in 2) These will be the customers who have purchased all available stocks.

Hope this helps.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: the list of customers who own all stocks

Michael Sync12-Nov-07 14:39

Michael Sync

12-Nov-07 14:39

Thanks... I will try and will let you know..

pmarfleet wrote:
1) CROSS JOIN Customers with Stocks to get every possible combination of customers and stocks.

This line will be the same as all data from CustomersStocks.

pmarfleet wrote:
RHS

RHS stand for?

Re: the list of customers who own all stocks

pmarfleet12-Nov-07 19:41

pmarfleet

12-Nov-07 19:41

Michael Sync wrote:
This line will be the same as all data from CustomersStocks.

No it won't. CustomersStocks contains combinations of customers and stocks where the customer has bought the stock. Cross joining Customers and Stocks returns the cartesian product of the two tables, ie. every possible combination of customers and stocks regardless of whether a customer has actually bought the stock. The purpose of this is to compare the difference between this set and CustomersStocks to find which customers have not bought at least one of the available stocks. Once you have identified these customers, by process of elimination you can find the customers who have bought all available stocks.

Michael Sync wrote:
RHS stand for?

Right-hand side.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

problem with stored procedure

suhailahmedmohammed12-Nov-07 3:57

suhailahmedmohammed

12-Nov-07 3:57

Hi,
i want to write stored procedure insuch a way where in it has to return the table name used stored procedure.
for example
create procedure proc1
as
begin
select * from student
end
once u exit exec proc1 it has return the table name used i.e "Student"
could you please help in doing this.
or it is possible in sql.
reply me soon at suhailquadri@gmail.com Big Grin | :-D

Suhail Ahmed Mohammed, Application
Engineer,
Cybernet Slash Support

Re: problem with stored procedure

pmarfleet12-Nov-07 4:43

pmarfleet

12-Nov-07 4:43

I don't think there is any way to do this. Besides, is it necessary? Stored procedure are usually coded to select data from a particular table or tables. You would know which table(s) each stored procedure used, so you could use an OUTPUT parameter to return a varchar value which was hard-coded to that table name for each stored procedure.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Last Visit: 31-Dec-99 18:00 Last Update: 4-May-24 2:22

Refresh

ᐊ Prev 1...1343 1344 1345 134613471348 1349 1350 1351 1352 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.