|
Here's what I know about 1st to 3rd NF:
1st NF: No repetition.
Make sure there are no repetition of data or multivalue in single column (e.g. 2 phone numbers in one column "9999 9998, 9999 9999"). That means you may have to split the table into several tables until there is no repetition of data.
2nd NF: No partial dependency.
Check tables with compound PKs (PKs consist of 2 or more column). If there is a column that depends only on a part of the compound PK then you have to put it in a new table e.g. table with columns
StudentID, ClassID, Grade, StudentName
has to be separated into
StudentID, ClassID, Grade
StudentID, Student Name
as StudentName is only dependant on StudentID, not both StudentID and ClassID (the composite PK) hence the partial dependency.
3rd NF: No transitive dependency.
Check that there are no columns (including the PK) that is dependant on a non-PK column e.g. table with columns
EmployeeID, EmployeeName, ManagerID, ManagerName
has to be separated into
EmployeeID, EmployeeName, ManagerID
ManagerID, ManagerName
as ManagerName is dependant on ManagerID (non-PK) hence the transitive dependency.
Hope it helps! 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Actually, I've solved my prior problem, but what I can't seem to do is get one query that does everything I want. I have two queries, that return the same columns, but one returns where a primary org is set, and one returns an arbitrary org for the contact where there is no primary. How do I turn these two tables, with the same columns, into one ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Use UNION?
e.g.
SELECT Column1, Column2<br />
FROM qry1<br />
UNION<br />
SELECT Column1, Column2<br />
FROM qry2
Edbert P.
Sydney, Australia.
|
|
|
|
|
I've heard that I can use SQL to run dynamically changing mathematical equations. I've got the equation stored in a string (i.e. "((100-75)/100)*100") with the values randomly changing. the formula will not stay constant either, but it is the example I am using. How do I get this to produce a result that I can store in another string? Thanks.
Nick
|
|
|
|
|
ColdWaterBlue wrote:
I've heard that I can use SQL to run dynamically changing mathematical equations.
It has the potential. You could create some dynamic SQL and execute it.
EXEC ('insert the dynamic sql between the apostrophes')
The final solution depends on the source of the data (which table(s) in the database, and which columns) and how dynamic you want your forumulas. For instance, you could set up a table to hold the formulas.
You are probably looking at writing some stored procedures to do the properly.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hello All, I wrote the code below planning to use it to pick out the newest database log file in a certian directory on a computer on our LAN.
<br />
Function NewestFile(Directory, FileSpec)<br />
' Returns the name of the most recent file in a Directory<br />
' That matches the FileSpec (e.g., "*.xls").<br />
' Returns an empty string if the directory does not exist or<br />
' it contains no matching files<br />
Dim FileName As String<br />
Dim MostRecentFile As String<br />
Dim MostRecentDate As Date<br />
If Right(Directory, 1) <> "\" Then Directory = Directory & "\"<br />
FileName = Dir(Directory & FileSpec, 0)<br />
If FileName <> "" Then<br />
MostRecentFile = FileName<br />
MostRecentDate = FileDateTime(Directory & FileName)<br />
Do While FileName <> ""<br />
If FileDateTime(Directory & FileName) > MostRecentDate Then<br />
MostRecentFile = FileName<br />
MostRecentDate = FileDateTime(Directory & FileName)<br />
End If<br />
FileName = Dir<br />
Loop<br />
End If<br />
NewestFile = MostRecentFile<br />
<br />
End Function<br />
However, the directory that I need to pass to this function when called is a filepath on a computer on the LAN and the directory I need to access is password protected. Can anyone help me out on how to do authentication using VBA code in an access DB. Thanks is advance.
Frank
|
|
|
|
|
Hey guys...
I have a very simple question with probably a very difficult answer (for me  )
I have a view, containing three fields I want to create a crosstab with.
A Date (should be the 'row' header)
A Name (should be the 'column' header)
and a Total (number) field.
Can anyone 'introduce' me into SQL, cause i'm very very very new into these kind of queries (probably SP's)
I love it when a plan comes together
|
|
|
|
|
Either install Reporting Service to SQL Server or endure the agony I had to by using the following techniques. Take a look at the following article which will do the cross tab queries. I implemented them with success.
http://www.sqlteam.com/item.asp?ItemID=2955[^]
Good Luck
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Does anyone know of any good ( and preferably free  ) programs that will compare two instances of an Oracle db and generate an upgrade script of the differences including existing data?
|
|
|
|
|
Hi all,
For SQL2000 or SQL CE, if one record in the table is locked by a client
with SQL command, then if any other client access the locked record, will the SQL server return exception or the SQL server will let the client wait until the record is unlocked ?
Thanks
|
|
|
|
|
Is it possible work with ADO and MySql Database in VB6?
|
|
|
|
|
paykani wrote:
Is it possible work with ADO and MySql Database in VB6?
Yes, You need to get the ODBC drivers from the MySQL website. Once they are installed, Use ADO to connect via ODBC as the data source.
-----------------------------
Social Engineering Specialist.
Because there is no patch for human stupidity.
|
|
|
|
|
Hello!
I have a problem sorting columns (not rows!) in DataSet. Each column in my DataSet represents a month. I get a filled DataSet, but I want my columns to be sorted by months(january, february, march etc.) before I bind it to DataGrid. How can I sort them? Is it posible to sort columns in a DataGrid?
Thanks for the answers.
Jani
|
|
|
|
|
|
Yes, I have considered sorting columns this way, but it has no effect, because I have a query in a for loop, and query is different each time the loop iterates. So I fill DataSet each time I iterate through the loop. The result is unordered DataSet. So, I need ordered DataSet.
|
|
|
|
|
|
Hi
Is there any problem if 2 threads use the same database SQL Server CE 2000 connection do transaction at the same time?
If 1 threads capture the right to do the transaction, then is the other thread will wait for access or what others happen?
Thanks
|
|
|
|
|
I'm assuming that SQL Server CE 2000 is sufficiently similar to the full SQL Server 2000 product that this answer is valid - Considering you are using a cut down version I cannot see how it would have enhanced functionality. So here goes...
If you use 2 threads and operate on the same connection then you have to be careful only to run one command at a time on the connection. Remember that the DataReader is running the command until you exhaust the data being returned. If you attempt to run a second command at the same time as an existing command is running an exception will be thrown.
So, the answer is: It won't wait, it will throw an exception.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Then how about 2 thread hold 2 objects respectively and the objects contains a database connection respectively and the 2 connections are different object reference but they also connect to the same database?
Will exception be thrown if the 1 object is doing transaction and the other object try to connect to the database?
|
|
|
|
|
|
Following a number of MSDE installations I am finding that whenever the client re-boots or shuts down the server / PC the server connection settings are lost and have to be manually re-entered to restart the server. Can anyone help with where this is controlled from?
I have a local installation from the Microsoft SQL Server Personal Edition CD and don't have this problem. I seems to be associated with the MSDE install.
Thanks,
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
|
Sorry, I don't respond to direct emails -
Axsys wrote:
Hi Colin,
Thanks for the reply.
The MSSQL service is running OK, the problem I'm having is with the SQL Server Service Manager where the Server Name and Services details are entered.
Each time the PC is rebooted / shut down these details disappear and the Service Manager has to be opened and the details manually re-entered.
I did another MSDE install on an XP box yesterday and it was fine, but I've got two installs (both on XP boxes) where these details are not being retained?
Any clues would be appreciated.
Thanks,
Kerry
Kerry Johnson
Business Systems Consultant
The Service Manager doesn't have anything to do with the running of SQL Server, it is just a simple management tool to start, pause/continute and stop SQL Server processes without going in to the Services snap-in within the Admin Tools.
If I look in my services manager I see a (seemingly) random selection of SQL Servers that are on the network - I can type in any missing details and then Start/Stop them.
If you are using the services manager to restart your SQL Server on reboot I would suggest check the "Auto start service when OS Restarts" box. Or, go into the Admin Tool-->Services and find the service called MSSQL (If you have a named SQL Server instance, it is called MSSQL$instance_name) and right-click and select properties. In the dialog, set the Startup Type to Automatic.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hello ADO world user
How do I get the row_index of the table after using the find command.
e.g.
row = table.Rows[row_index];
row = ds.tables["tablename"].Rows.Find(Id.ToString())
and now how to get
row_index = ?????
Some ideas ?
Thank you in advance
fracalifa
|
|
|
|
|
I am trying to determine if there is a way to ensure that one field gets updated before another in my stored procedure. I am finding that I usually have to run the sproc twice to get the values that I want, but it seems to me that there must be an easier way. In the sproc below, I want to ensure that Profit gets updated, but I don't know how. Any ideas?
CREATE PROCEDURE [dbo].[UpdatePricePoint]
(@StoreID [int],
@StoreDate [datetime] )
AS
BEGIN
UPDATE TicketItems
SET
Cost =
(CASE WHEN TicketItems.Qty = 0
THEN
1
ELSE
TicketItems.Qty
END )
*
(CASE WHEN StoreItems.CurrentCasePack = 0
THEN
StoreItems.CurrentCaseCost
ELSE
StoreItems.CurrentCaseCost / StoreItems.CurrentCasePack
END),
NormalPrice =
CASE WHEN StoreItems.NormalMSU = 0
THEN
StoreItems.NormalUnitSell
ELSE
StoreItems.NormalUnitSell / StoreItems.NormalMSU
END,
NormalAmount =
(CASE WHEN StoreItems.NormalMSU = 0
THEN
StoreItems.NormalUnitSell
ELSE
StoreItems.NormalUnitSell / StoreItems.NormalMSU
END)
*
(CASE WHEN TicketItems.Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
PricePoint =
(TicketItems.Amount - TicketItems.DiscountAmount)
/
(CASE WHEN Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
Price =
TicketItems.Amount
/
(CASE WHEN Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
Unit = (CASE WHEN TicketItems.Flag2 = 16 OR TicketItems.Flag2 = 32 OR TicketItems.Flag2 = 64
THEN
1
ELSE
TicketItems.Qty
END),
NormalMSU = StoreItems.NormalMSU,
MSU = StoreItems.CurrentMSU,
Profit = TicketItems.Amount - TicketItems.Cost
FROM TicketItems
INNER JOIN StoreItems
ON
TicketItems.StoreID = StoreItems.StoreID AND
TicketItems.Plu = StoreItems.Plu
INNER JOIN TicketTotal
ON
TicketItems.StoreID = TicketTotal.StoreID AND
TicketItems.TicketDate = TicketTotal.TicketDate AND
TicketItems.TicketTime = TicketTotal.TicketTime AND
TicketItems.POSID = TicketTotal.POSID AND
TicketItems.TicketID = TicketTotal.TicketID
WHERE
TicketTotal.StoreID = @StoreID AND
TicketTotal.StoreDate = @StoreDate
END
Notice that I set pofit last, however, it is still null on the first run so I can only assume that it is updatint profit before cost is getting updated.
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
