|
|
So your assignment table actually looks more like this:
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo
astv wrote: How do I change the design to I only use one table for "Create By:" and "Assign To:"?
You also mention that you want to record if a person reassigns a task. I would guess that should go in the ASSIGN_LOG table. You may want to add a couple of nullable columns for that. You might also want to keep the current assignee in the ASSIGNMENT table for performance reasons.
Also, I don't know what you mean by "only use one table for "Create By:" and "Assign To:". They already are in one table.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Yes the assignment table looks exactly like how you have it.
Ok, if I understand you correctly, when an entry is made in the ASSIGNMENT table, an entry is also made in the ASSIGN_LOG table. This would make sense since my intent for the ASSIGN_LOG table is to keep assignment historicals.
as for The use of single table for "CreatedBy" and "AssignTo" question:
Currently, in my design I use the USERS table to populate the CreatedBy field and the STAFF table to populate the AssignTo field.
USERS
USERS_SID [PK]
USERNAME
LAST_NAME
FIRST_NAME
STAFF
STAFF_SID [PK]
STAFF_USERNAME
ASSIGNMENT
A_SID [PK]
STAFF_SID [FK]
PRIORITY_SID [FK]
TASK_SID [FK]
DEPT_SID [FK]
USERS_SID [FK]
A_TITLE
A_DESC
A_DATE
CreatedBy
AssignedTo
Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?
STAFF
STAFF_SID [PK]
STAFF_USERNAME
ASSIGN_LOG
A_LOG_SID [PK]
STATUS_SID [FK]
A_SID [FK]
A_LOG_DATE
A_LOG_NOTES
RESOLVE_DATE
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Is this ideal to use two different tables storing basically the same information? Should I change the STAFF table relationship to ASSIGN_LOG table?
I was guessing that CreatedBy and AssignedTo would be staff ids (or it could be user ids)
Actually, what is the difference between staff and user in this context?
So in Assignment what does USERS_SID represent? What does STAFF_SID represent? And CreatedBy and AssignedTo? (becuase I'm guessing about all of this)
You aren't, as far as I can see, using two tables to store basically the same information. The Log table shows the historical information, the Assignment table shows the current state along with other information. If you are writing queries that are not interested in historical data then you don't want an additional join to the log table because it will slow things down. It would also be quite a complex join because you'd need to join on the most recent only.
Whether you duplicate the current state in the historical log or not is up to you. I would because it means when you are looking for everything upto and including the current it is there.
So, you might want to set up a trigger on Assignment to INSERT a row in the log to ensure that it is always up-to-date. The minor duplication of data isn't a problem if it is properly managed.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
In ASSIGNMENT table, USER is the CreatedBy and STAFF is the AssignedTo information. The SID is to prevent freeform entry. a combo box or boxes will be populated with with usernames and the SID is what is passed to the ASSIGNMENT table. I think I am duplicating entries again. I do not think I need the AssignedTo and CreatedBy fields if I am going to keep the USER and STAFF tables.
Do you think I should move the STAFF relationship to ASSIGN_LOG?
I like the trigger idea to INSERT a row in the log to ensure it is up-to-date.
Thanks,
Steve C.
kstv@netzero.net
|
|
|
|
|
astv wrote: Do you think I should move the STAFF relationship to ASSIGN_LOG?
Copy maybe, but not move. It would be good to log that information, but you'd need the current status available also.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hey,
I'm using this database through MS Access with my program and I need to be able to use the LIKE keyword. My forms have specific, formatted field names that are sequential and uniform. Essentially, the first part of every form field name is a part of its subsequential heading.
IE.
I have a 'heading' column in my table, 'paragraphs'. One example of my headings is 'Scope of Services & Workplan/Methodology Overview'.
Now, if I were to use a query searching for that exact name everything works fine. However, I need to use the LIKE clause as follows:
SELECT heading FROM paragraphs WHERE heading LIKE 'Scope%'
This is because the first part of my form fields are the first words of every heading. ie. Scope_1_chbx, Scope_2_chbx, and so on.
My LIKE clause is resulting in the query returning no results....can anyone help me? Thanks.
---
reegan41
|
|
|
|
|
Your select statement looks okay to me. Perhaps your data is the problem. I've had problems with the LIKE feature because of selecting LIKE 'ABC%' when my data was in lower case, LIKE 'abc%'.
If that is not the case, could you please explain what you are doing further? What do you mean with the Scope_1_chbx, Scope_2_chbx stuff?
Could you give us some actual data from the tables?
Hope this helps.
|
|
|
|
|
I actually figured it out...believe it or not, it was because I was using LIKE 'data%' instead of LIKE "data*"
For some reason THAT worked instead...odd, but thanks for your help regardless
|
|
|
|
|
You were using an MS Access database? What version?
|
|
|
|
|
I have following error in window service same code is working in window Application
"QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed."
Dim objbl As SQLXMLBulkLoad3
objbl = New SQLXMLBulkLoad3
objbl.ConnectionString = "Provider=SQLOLEDB.1;Data Source=pcDB;Initial Catalog=webdb;User ID=sa;pwd=sa"
objbl.ErrorLogFile = "c:\error.log"
objbl.BulkLoad = True
objbl.KeepIdentity = False
objbl.CheckConstraints = False
objbl.Transaction = False
objbl.Execute("c:\Items.XSD", "C:\Items.xml")
Thanks
Hardik Panchal
|
|
|
|
|
Don't cross post in multiple forums. It's considered very rude to "spam" the forums and makes it quite difficult for multiple people to collaborate on an answer.
|
|
|
|
|
Hi,
I want to improve the following query's performance executing on sqlce.
select *
from TABLE_A
where field_a in (select field_a
from TABLE_B
where field_b = '0000162080'
and field_c = '2'
and field_d = '011')
order by field_e
Table_A has index for field_a and Table_B has index for field_b and field_c. How can I rewrite the query to execute faster?
thanks,
- ferudun -
|
|
|
|
|
I don't use SQL CE, but the principles are generally the same for different database products:
Do you have separate indexes on field_b and field_c? If you do then try using a composite index instead:
create index table_b_idx2 on table_b (field_b, field_c, field_d, field_a)
Also, you may be able to get the query to run faster by changing the "select *" bit so that it just returns the columns that you actually need to use.
|
|
|
|
|
I have a table :
ID ___ IDUser ___ Year
1 ___ 1 ___ 2005
2___ 1 ___ 2007
3 ___ 1___ 2003
4___ 2___ 2008
5 ___ 2___ 2005
I want to filt : unique IDUser with Max year :
ID ___ IDUser ___ Year
2___ 1 ___ 2007
4 ___ 2 ___ 2008
Please help me. Thanks.
|
|
|
|
|
Something like this:
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Error s: SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year
Exception : "Column IDUser is invalid in the select list because it is not contained in erther an aggregate function or the Group by clause"???
|
|
|
|
|
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY IDUser) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
It is OK. Thanks you very much !
|
|
|
|
|
Ah... Sorry - I mistyped the query. Glad you figured it out.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
select A.CategoryName, B.CustomerId , sum(A.CategoryId) as SumOfCID from Categories A , Customers B
the above query makes the following error
you tried to execute a query that doesn't include the specified expression 'CategoryName' as part of an aggregate function.
i am using vb6 and access
-----
thanks
|
|
|
|
|
use GROUP BY clause
select A.CategoryName, B.CustomerId , sum(A.CategoryId) as SumOfCID from Categories A , Customers B GROUP BY A.CategoryName, B.CustomerId
request you to pls check GROUP BY clause usage in SQL (language)
hope you are not aware
Note:
you are fetching data from two table and there is no join specified.
Regards
KP
|
|
|
|
|
how to use auto increment property in ASP.NET
|
|
|
|
|
Auto increment could be done in sql server 2000 by using identity.
You wanted in ASP.Net, could you explain in brief what exactly you are looking for?
Gautham
|
|
|
|
|
I need to connect the MySql database. Can anyone tell me what's the namespace to use for the connection and how do to connect .
Regards,
LEE
|
|
|
|
|
Create ODBC connection and create DSN for ur MySql DB.
rahul
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
