Many To Many

hi im implementing a database in ms access to migrate it later to SQL, its a project tracking/ employee tracking and im having trouble with some of the tables... the relationships are as follow

Employee : M
Employee_ID
Name
Phone
Supevirsor_Name
Supervisor_Email

Project : M
Project_ID
Project_ Name
Description
Project_Added

EmployeeProject
Employee_ID
Project_ID
AssignedBy

i made this third table called EmployeeProjects for the relationship, but when i go to collect the data everything is ballistic, when i go to capture employees in the employee table everything is fine, i go to the projects and everything is fine there is a "+" in the projects its lists every single employee that i captured in employees in the same project i go to the next record and the same deal, there is a possibility that this could be that many employees can be in a project and also working in another project, what is wrong with it? can anybody help me?I'd say nothing...

Why don't you post the DDL for the tables (CREATE TABLE myTable99(Col1 int, ect)

Some sample Data (INSERT INTO myTable99(Collist) SELECT Data UNION ALL SELECT ect)

The DML You've attempted (SELECT Col1, Col2 FROM myT INNER JOIN myt2, ect)

And the results you'd expect...

I'd say you'd get an answer in 15 minutes of that post...|||The designn looks OK. Id' change your Employ3ee table a little though:

Employee_ID
LastName
FirstName
MiddleName
FullName (calculated...not sure if access has that)
PhoneArea
PhonePrefix
PhoneSuffix
Phone (calculated...same as FullName)
EMail
Supervisor_ID (NULL or same if it's a supervisor)

The reason you may have the situation you describe is only because of the contents of EmployeeProject. Do this:

select Project_ID, count(*) from EmployeeProject group by Project_ID
union all
select 0, count(*) from Employee

This will get you started on finding out how many employees are assigned to each project and what's the total number of employees assigned vs. the total number of employees in the organization.|||I don't have any SQL procedures that can parse that last sentence of yours, so I'm not exactly sure what the problem is.

But I have to ask why you are developing this in MS Access if you are planning to upsize it to SQL Server anyway. Have you considered creating it in SQL Server and using an Access Data Project (.adp file) front-end? You'd get all the benefits of Access forms, reports, and modules for the interface, and you wouldn't have to upsize it later. Plus SQL Server's security is much better and easier to implement than MS Access security.|||the reason for me doing it in access first is my boss maily.... hes a manufacturing engeneer and he knows nothing about servers and all that... he asked me to do it first kinda like a prototype for collecting the data... i said prior to him that i could build it in SQL save us a hole lot of time.. but he wouldnt budge... (putz)... any way i explained that particular situation ( me using access as a front end) but he was like no no Y complicate it so much... just doit in access and then we will see what to leave or what not... any ways thanx for your help...|||i actually did that... in fact i did get the results that i wanted... but also... i dropped the tables and created them again... exactly and got them as i wanted... your solution was indeed good just got to it now... if only i'd gotten to it sooner... thanx for your help u really did help me|||Exactly what i was doing... but also i don't know what in the heck was wrong with the tables... so i'd dropped'm and built them again got what i wanted... what u posted... was what i did and it worked... thanx|||Your boss is a putz. Oh wait, you already said that. Well tell him I said so too.

Asking him why he bothers hiring competent people if he isn't going to trust their expert judgement. Duh.

Manipulate the 'deleted record' flag on DBF files

Hi,

I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.

These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

What are the options available for me to be able to query and differentiate the 'deleted' records ?

Thank you in advance,

Bertrand Larsy

Hi Bertrand,

Here's some example VB code to work with the deleted status of a row:

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
'-- Make some VFP data to play with
Dim cmd1 As New OleDbCommand( _
"Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestDBF Values (1, 'Hello')", cn1)
Dim cmd3 As New OleDbCommand( _
"Insert Into TestDBF Values (2, 'World')", cn1)
Dim cmd4 As New OleDbCommand( _
"Delete From TestDBF Where Field1 = 1", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()
cn1.Close()

Dim cn2 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn2.Open()

Dim cmd5 As New OleDbCommand( _
"Select * From TestDBF", cn2)
Dim da1 As New OleDbDataAdapter(cmd5)
Dim ds1 As New DataSet
Dim dr1 As DataRow
da1.Fill(ds1)
For Each dr1 In ds1.Tables(0).Rows
Console.WriteLine( _
dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
Next
Console.ReadLine()
cn2.Close()

Dim cn3 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn3.Open()

Dim cmd6 As New OleDbCommand( _
"Set Deleted Off", cn3)
cmd6.ExecuteNonQuery()
Dim cmd7 As New OleDbCommand( _
"Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
Dim da2 As New OleDbDataAdapter(cmd7)
Dim ds2 As New DataSet
Dim dr2 As DataRow
da2.Fill(ds2)
For Each dr2 In ds2.Tables(0).Rows
Console.WriteLine( _
dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
Next
Console.ReadLine()
cn2.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

|||

Thank you,

This is indeed a workable solution.

Now, is there a way to perform the same work in a T-SQL script ?

I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

SELECT [name]

FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

This returns all records, but not the deleted ones

SELECT [name]

FROM OPENQUERY(linkedServer,'SET Deleted OFF;

SELECT name FROM Members')

This produces the error '

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "SET Deleted OFF;

SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

'

When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

DECLARE @.Query varchar(max)

SET @.Query='SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This returns all records, but not the deleted ones

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This produces the query output 'Command(s) completed successfully.', but does not return any result.

Kind regards,

Bertrand Larsy

|||

Hi Bertrand,

I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

|||

Neither of the 2 following samples give any result:

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

Kind regards,

Bertrand Larsy