|
I have the following tables (I left out fields that don't apply to this question).
Employees, Stores, and Groups. Each employee can belong to one or more Stores, and each Store is in one Group
Employees
EmployeeId Number
Certified DateTime
EmployeeStores
EmployeeStoreId Number
EmployeeId Number
StoreId Number
Stores
StoreId Number
GroupId Number
I need to produce a query that displays the percentage of the Certified field completed in the Employees table by Group. So, assuming Group A, Group B, and Group C, the result set should have:
Group A 13
Group B 42
Group C 21
Other 24
Since I'm very new to Access, I could use some help forming this query.
Thank you
Everything makes sense in someone's mind
|
|
|
|
|
Anyone have any ideas why this won't work? I just want to check whether some fields are not null before I set the value of @UserName
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_ID('[dbo].[__Reporting_GetUserName]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[__Reporting_GetUserName]
END
BEGIN
EXEC dbo.sp_executesql N'
CREATE FUNCTION [dbo].[__Reporting_GetUserName] (@userId int)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @UserName VARCHAR(MAX)
SET @UserName =
(
IF [FirstName] is not null
BEGIN
SELECT [FirstName] + '' '' + [LastName]
FROM __User
WHERE Id = @userId
END
)
RETURN @UserName
END
'
END
|
|
|
|
|
Why not use a CASE?
SELECT CASE WHEN [FirstName] is not null THEN [FirstName] + '' '' + [LastName] ELSE null END FROM __User WHERE Id = @userId
(Or something.)
|
|
|
|
|
Etienne_123 wrote: Anyone have any ideas why this won't work?
What would it fill the variable with if FirstName does equall null? You'd better assign it directly without the IF statement, and use the <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx">ISNULL</a>[<a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" target="_blank" title="New Window">^</a>] function.
Bastard Programmer from Hell 
|
|
|
|
|
I am working on a simple game where you take creatures into battle and they fight against each other. Everything is working fine minus figuring out the best way to store the results.
I want something similar to this data structure.
int ID (identity)
int AttackerID
int DefenderID
int AttackerCreatureID1
int AttackerCreatureID2
int AttackerCreatureID3
int DefenderCreatureID1
int DefenderCreatureID2
int DefenderCreatureID3
and then more for the creatures that died, ect
The problem is, I want the characters to be able to buy more battle slots and bring more into battle at a time. So, say they know can bring 5 creatures in, I don't want to have to make a bunch of new columns. Using XML this exercise would be pretty easy since you can just add a couple extra tags here and there whenever you want.
Like:
<AttackerCreatures>
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>
</AttackerCreatures>
I've done lots of work with SQL before but never come across doing something like this with variable amounts of data column wise...
Any help would be awesome! Thanks ahead of time.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
You could try this:
Procedure with following params
int ID (identity)
int AttackerID
int DefenderID
NVARCHAR AttackerCreatureIDs (this would be comma separated ids)
NVARCHAR DefenderCreatureIDs (this would be comma separated ids)
then in your proc, split the Ids string
the below function could be used
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
GO
|
|
|
|
|
I thought about doing that but it feels dirty, doesn't feel very relational databaseish...
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
It looks to me as if you've got a many to many relationship there. A battle joins many attackers to many defenders. So you would have a table Battles which has a unique BattleId, plus AttackerId and DefenderId plus whatever other information you want to hold against a battle (date, location, whatever).
Then you have a number of options. One option would be to have an Attackers table which holds BattleId and CreatureId for all the attackers, and another table Defenders which holds BattleId and CreatureId for all the defenders. Or another option would be to have just one table BattleCreatures which holds BattleId, ArmyId and CreatureId where ArmyId is either the AttackerId or the DefenderId depending which side the creature is on.
Whether you separate out Attackers from Defenders into two separate tables or whether you have them all together in one table is a bit of a judgement call. There are arguments for and against both options.
|
|
|
|
|
i have two tables
ROOM(nbROOM primary key) : ML002
STUDENT(nbROOM) : ML002
How can i change the ROOM(nbROMM) : ML002 => ML003
=========> the STUDENT(nbROOM) must also change : ML002 =>ML003 ?
|
|
|
|
|
Simple - you can't.
The first problem is that you are using business data for keys. And you now know why that is a problem.
Your only solution now requires.
1. Create a new room row with ML003
2. Update STUDENT to point to MLO03.
3. Delete the existing ML002 record.
Second problem is that what you are doing isn't logical anyways. If you have a list of rooms you don't change them unless you are in fact relabeling all of the rooms themselves (ie someone is going down the hallway in the school and putting new numbers on the doors.)
In contrast if a person is assigned to the wrong room or needs to be moved then you update the person, not the room, to indicate the change. (In databases in general there is another way to do this but it wouldn't apply to room assignment.)
Your solution should be
ROOM(primary key, room label): {1, ML002}, {2, ML003}
STUDENT(room primary key): {1}
In the above you would just update '1' in the student record to be a '2'
|
|
|
|
|
^^~ u didn't understand my problem
ROOM(nuROOM primary key, nameROOM)
STUDENT(nuSTUDENT primary key,nameSTUDENT,nuROOM)
this is my database !
|
|
|
|
|
What JSchell is saying is that your database is designed WRONG and you should fix it before you get into even more trouble. Whats more he gave you the ideas of how to fix it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
lyngocquy wrote: u didn't understand my problem
No you don't understand my reply.
1. Your database design is wrong.
2. This problem is a specific example of why it is wrong.
3. I told you how to do the update with your existing design.
|
|
|
|
|
I am assuming you have a foreign key constraint that is preventing you from modifying the primary key. In that case, insert new records that are based on the existing records, but with the new primary key, then delete the old records.
|
|
|
|
|
It's called "cascade on update", and it's a bad habit. You'd really provide an artificial key to make the reference. It'd be wise to use an alternative unique constraint for the primary identification of the BO.
Bastard Programmer from Hell
|
|
|
|
|
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc.
There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB.
It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties.
Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing.
Thx
Mark Jackson
|
|
|
|
|
mjackson11 wrote: Is there any known issue that causes this behavior?
Yup. Access tries to get everything into memory.
But why stick to Access? You could migrate away from a bloated client to a cleaner UX using just about any other language. I think VB3+ODBC gave better control then Access [as a UI] ever did.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
Have a bit more patience with newbies. Of course some of them act dumb -- they're often *students*, for heaven's sake. -- (Terry Pratchett, alt.fan.pratchett)
|
|
|
|
|
Can you post a sample of the code which is causing Access to crash ?
Are you accessing the SQL tables via Access linked tables ?
Are you sure that you are fetching only the data you need and not entire tables ?
Maybe I can offer some options that can get you over these pain-points without an entire application re-write.
|
|
|
|
|
Oh so now YOU want him to snd codz plz 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is no source code as the tables are linked directly to the SQL server. Access crashes if you try to open the table. It works for smaller tables but once you get past 50-60 MB it gets very dicey about crashing.
I suspect it is pulling entire tables.
|
|
|
|
|
mjackson11 wrote: What do other people do?
Typically no one with any choice would use Access as a front end, you are going to have to upset your users, unless that is you want to write the UI to match the look and feel of Access. I can think of worse fates for a developer but not many.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
mjackson11 wrote: What do other people do?
Per the other suggest that MS Access attempts to load all of it....
If that is the case then create a view(s) that limits the data set greatly. And link to that.
|
|
|
|
|
Hello,
And thanks in advance for the help.
I am using this query to group exams and count:
SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count]
FROM
[DB].[dbo].[Accessions] A
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID]
INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID]
INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID]
WHERE
--XRAY,US,PICC,EKG
(BPG.[GroupName] IN ('XRAY','EKG','US','PICC'))
AND
(F.[Name] LIKE '%' + @Facility + '%')
AND
(A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year)
AND
(A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run'))
GROUP BY BPG.[GroupName]
Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. 
Thanks for the help.
|
|
|
|
|
Try to change join condition and see if you get result which you need.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thank you. I am not sure what to change and that is why I asked for help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
