Insert Into? Delete? Update?

**NeoPa** · Jan 28 '07, 02:24 PM

I'm having difficulty following what this is all about.
I understand some of it, but not how the data fits together :(
Can you post your table structure (please feel free to ignore any irrelevant fields as before) for all the relevant tables as well as indications (PK; FK; etc) of how each links to any others. Here is an example table MetaData layout that you could follow :

Posting Table/Dataset MetaData

Code:

[b]Table Name=tblStudent[/b]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

**Craash** · Jan 28 '07, 04:34 PM

Originally posted by NeoPa

I'm having difficulty following what this is all about.
I understand some of it, but not how the data fits together :(
Can you post your table structure (please feel free to ignore any irrelevant fields as before) for all the relevant tables as well as indications (PK; FK; etc) of how each links to any others. Here is an example table MetaData layout that you could follow :

Posting Table/Dataset MetaData

Code:

[b]Table Name=tblStudent[/b]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

Code:

tbl_Assets
fld_AssetID; AutoNumber; PK
fld_EmployeeID;Number;FK; Combo Box;
Query:SELECT tbl_Employee.EmployeeID,tbl_Employee.LastName & ", " 
& [FirstName] AS Expr1 FROM tbl_Employee ORDER BY tbl_Employee.LastName & ", " & [FirstName];

fld_Available; Yes/No; Check Box

[B]tbl_Employee[/B]
fld_EmployeeID; AutoNumber; PK
fld_StatusID; Number FK ;Combo Box; 
Query: SELECT lutbl_Status.*FROM lutbl_Status ORDER BY lutbl_Status.Status;                                           

fld_FirstName; Text
fld_LastName; Text

[B]tbl_Status[/B]
StatusID; AutoNumber PK
Status: Text

Relationships Between the tables are

tbl_Employee                 tbl_Assets
EmployeeID ------------------EmployeeID PK
                 1             Many
                Enforce RefInteg
      	Join Properties 2: Include All records from tbl_Employee and only
                those records from tbl_Assets where the joined fields are equal.

tbl_Employee                      tbl_Status
StatusID -------------------- -------StatusID
             Many                   1
                      No RefInteg


There are a couple forms for data entry;
frmEmployeeInfo -- Enter Employee info fields bound to tbl_Employee
frmAssetInfo    -- Enter Asset info and assigned asset to Employee


The next form is where I can view assigned assets to an employee
frmAssignedAssets -- Employee info displayed with a datasheet showing                  assigned assets to an employee
 TextBox: FirstName Control Source is tbl_Employee.FirstName
 TextBox: LastName Control Source is tbl_Employee.LastName
 ComboBox:STATUS:Control Source is tbl_Employee.StatusID

dsAssignedAssets -- Source Object dssubfrm_Assets (which source object 
is tbl_assets)
                    Link Child Fields; EmployeeID
                    Link Master Fields; EmployeeID

I would like to be able to change the Status on this form to Inactive which would clear the tbl_Assets.Empl oyeeID field and change the tbl_Assets.Avai lable field from No to Yes...

The logic or lack there of is to be able to query the assets table for available assets for reassignment... Maybe I am doing this all wrong I wasn't sure if maybe I should create another table to move the assets to when they become available.
Well I am sure this is probably far more confusing than is needs to be. Sorry for the migraine I just caused you. :)

**NeoPa** · Jan 28 '07, 04:44 PM

Yes, I was hoping for something a little more straightforward :(
However, some problems are just not simple - What can you do?
Assuming everything I need is here, I'll have a look later on to see if I can find something for you. If not I'll post what I need.

**NeoPa** · Jan 29 '07, 12:23 AM

I'm going to try to post a résumé of the situation as far as I understand it.
I may update this in future (to keep it in one place) if you let me know I've misunderstood anything or if anything needs to be added. I've assumed that table lutbl_Status should actually be tbl_Status as defined below.

Code:

[b]Table Name=tbl_Assets[/b]
fld_AssetID; Autonumber; PK
fld_EmployeeID; Number; FK; Combo Box;
    Source: SELECT EmployeeID,LastName & ', ' & FirstName
            FROM tbl_Employee
            ORDER BY LastName,FirstName;
fld_Available; Check Box

Code:

[b]Table Name=tbl_Employee[/b]
fld_EmployeeID; AutoNumber; PK
fld_StatusID; Number; FK ;Combo Box; 
    Source: SELECT *
            FROM tbl_Status
            ORDER BY Status;                                           
fld_FirstName; Text
fld_LastName; Text

Code:

[B]Table Name=tbl_Status[/B]
StatusID; AutoNumber; PK
Status: Text

Relationships between the tables are such that the PKs are ones to the many FKs.

Code:

[b]Form Name=frmEmployeeInfo[/b] - Enter Employee info.  Controls bound to tbl_Employee
cboStatus; ComboBox; tblStatus
txtFirstName; TextBox
txtLastName; TextBox

Code:

[b]Form Name=frmAssetInfo[/b] - Enter Asset info and assign Asset to Employee
????

The next form is where I can view assets assigned to an employee.

Code:

[b]Form Name=frmAssignedAssets[/b] - Employee info.  Controls bound to tbl_Employee
FirstName; TextBox
LastName; TextBox
STATUS; ComboBox

Code:

???? dsAssignedAssets -- Source Object dssubfrm_Assets (which source object is tbl_Assets)
      Link Child Fields; EmployeeID
      Link Master Fields; EmployeeID

Not sure what this is about. I guess that it's related to a subform but no idea what it's bound to.
What type of objects are dsAssignedAsset s & dssubfrm_Assets? And how are they connected?
I'm going to post this modified version and see if viewing it laid out neatly helps me to understand this any better.
One question that might help clarify a few things.
What is a good definition of an Asset in your database?

**NeoPa** · Jan 29 '07, 12:30 AM

Originally posted by Craash

The scenario is when I change the Status on frm_AssignedAss ets from Active to Inactive
I would like to remove the value from the tbl_Assets.Empl oyeeID and change the value in
tbl_Assets.Stat usID to Yes and the same for tbl_Software.

I don't see any info for tbl_Software. This is like working with the light dimmed.
There is also no StatusID field in tbl_Assets according to your layout :confused:

**Craash** · Jan 29 '07, 01:31 AM

Originally posted by NeoPa

I don't see any info for tbl_Software. This is like working with the light dimmed.
There is also no StatusID field in tbl_Assets according to your layout :confused:

Whew, you’re good at understanding poorly defined information.

Ok here goes, I had omitted the tbl_software to avoid further confusion. Haha

The StatusID field represents an employee's status within the company. When the employee leaves the company the status would change from active to inactive.
With that change I am looking for a way to release the assets and software licenses assigned to that user.

The statusID field in tbl_employee would be the equivalent to the available field in the tbl_assets. So when the status changes to inactive the available field changes from No to Yes.

You have all of the tables defined correctly.

Assets

Code:

Table Name=tbl_Assets
fld_AssetID; Autonumber; PK
fld_EmployeeID; Number; FK; Combo Box;
    Source: SELECT EmployeeID,LastName & ', ' & FirstName
            FROM tbl_Employee
            ORDER BY LastName,FirstName;
fld_Available; Check Box

Software --- (The one I left out)

Code:

Table Name=tbl_Software
fld_SoftwareID; Autonumber; PK
fld_EmployeeID; Number; FK; Combo Box;
    Source: SELECT EmployeeID,LastName & ', ' & FirstName
            FROM tbl_Employee
            ORDER BY LastName,FirstName;
fld_Available; Check Box

Employees

Code:

Table Name=tbl_Assets
fld_AssetID; Autonumber; PK
fld_EmployeeID; Number; FK; Combo Box;
    Source: SELECT EmployeeID,LastName & ', ' & FirstName
            FROM tbl_Employee
            ORDER BY LastName,FirstName;
fld_Available; Check Box

Status

Code:

Table Name=tbl_Status
StatusID; AutoNumber; PK
Status: Text

The Forms

Enter in Employee Information (Has other information not relevant to this)

Code:

Form Name=frmEmployeeInfo - Enter Employee info.  Controls bound to tbl_Employee
cboStatus; ComboBox; tblStatus
txtFirstName; TextBox
txtLastName; TextBox

Enter in Asset Information

Code:

Form Name=frmAssetInfo – Enter Asset Info. Controls bound to Assets
cboAssetCategory; ComboBox; tblAssets.AssetCategory
cboEmployee; ComboBox; tblAssets.EmployeeID
chkbxAvailable;CheckBox; tblAssets.Available
Other Asset fields non relevant to this

Ok the most confusing form is the Assigned Software and Assets Form
The reason I created this form is to be able to view individual employee assigned assets and software.

It has the employee information displayed as non changeable.

Code:

Status : Active -----------------------------------------------This is the only changeable field
Employee Name                       Building Location
Department                              Department Manager
Extension                                Job Title

Then I inserted a Datasheet created from the assets table which displays

Asset ID | Asset Category | Model Number |    Memory    |  Video Card
   1              Computer         Inspiron1800      1GBDDR2     Radeon x800
   2              Printer              HP 5100                                                             


I did the same for Software Licenses, a Datasheet created from the software table

Software ID | License Type | Software Name | Serial Number
   1              Single User     Office 2003        xxxxx-xxxxx-xxxxx-xxxxx-xxxxxx
   2              Single User     Visio 2003          xxxxx-xxxxx-xxxxx-xxxxx-xxxxxx
   3              Floating           Inventor 11         xxxxx-xxxxx-xxxxx-xxxxx-xxxxxx

The assets that need to be tracked are Computers, Printers, Monitors, IPPhones etc…
The software is anything that has a serial number associated to it as many users have thousands of dollars in software available to them.

BTW…I have been reading through many of the forums and you-all are incredible for helping us challenged individuals. It is not for a lack of trying; I have only been doing this for less than a month and have my face buried in books trying to understand this stuff.

**NeoPa** · Jan 30 '07, 01:06 AM

I will try to get another run at this tomorrow volumes allowing. As it is relatively complicated it takes more time, so I have to lower the priority to keep as much running as possible.

**Craash** · Jan 30 '07, 02:22 AM

Originally posted by NeoPa

I will try to get another run at this tomorrow volumes allowing. As it is relatively complicated it takes more time, so I have to lower the priority to keep as much running as possible.

No problem NeoPa, I am thrilled that you are even considering helping me on this mess. :)
I am currently reading Access 2003 Programming by Example with VBA, XML, and ASP by Julitta Korol. Maybe I will figure it out and be able to post the solution (My goal)

I may just be approaching this all wrong, as stated earlier I have only been doing this for less than a month and am still becoming familiar with the concepts.

Let me know if you would like screenshots or any more information.

I fully understand if you just can't afford the time to consider this issue further.
Have a good evening :)

**NeoPa** · Jan 30 '07, 02:07 PM

Thanks for your understanding.
I will get to it if volumes allow & I don't fall asleep at the desk ;)
It's still flagged :)

**nico5038** · Jan 30 '07, 07:18 PM

Hmm, just another way of looking to your problem might be to use dates.
In general an employee should have a startdate and enddate.
The status "inactive" can be dropped in this case as you're able to select the users by their enddate.
The same start and enddate construction can be used for software and hardware licensies. The moment an enddate is entered for an employee you can select all hard- and software without an enddate and thus create a list for removing them.
Big advantage is that you have a trackrecord for this and even multiple licensies for software can be used when you add the startdate to the primary key of the software licensies, thus enabling multiple activations when needed. You could even allow to add an enddate for an employee "in the future" when you know it on forehand and thus take action on the exact date. Thus no one has to set a flag on the date itself.

Nic;o)

**NeoPa** · Jan 31 '07, 02:19 AM

Let us know what you think.
This will affect how we proceed from this point onwards.
for the record, I recommend following Nico's advice. It may seem a little more complex now, but because it is more fundamentally logical it may save you time with issues further down the line.

**Craash** · Jan 31 '07, 02:32 AM

Originally posted by nico5038

Hmm, just another way of looking to your problem might be to use dates.
In general an employee should have a startdate and enddate.
The status "inactive" can be dropped in this case as you're able to select the users by their enddate.
The same start and enddate construction can be used for software and hardware licensies. The moment an enddate is entered for an employee you can select all hard- and software without an enddate and thus create a list for removing them.
Big advantage is that you have a trackrecord for this and even multiple licensies for software can be used when you add the startdate to the primary key of the software licensies, thus enabling multiple activations when needed. You could even allow to add an enddate for an employee "in the future" when you know it on forehand and thus take action on the exact date. Thus no one has to set a flag on the date itself.

Nic;o)

Hello Nico,

Thanks for the advice. It does sound more logical and perhaps the best way to go. I wil give this a shot and see how far I can get. Thanks :)

**Craash** · Jan 31 '07, 02:37 AM

Originally posted by NeoPa

Let us know what you think.
This will affect how we proceed from this point onwards.
for the record, I recommend following Nico's advice. It may seem a little more complex now, but because it is more fundamentally logical it may save you time with issues further down the line.

Hi NeoPa,
I came to this forum looking for advice and help. If you guys believe that this is the best way then I will try to make this work. I will start making changes to the forms and the tables.
If you have any advice on how to proceed please let me know and as always thanks for your help :)

**NeoPa** · Jan 31 '07, 02:54 AM

You're the only one of us that knows your requirements and your current database that precisely. Nico's idea sounds very logical to me and, as stated, gives the extra logging dimension. From what I know that seems to be a better way forward.
Having thoroughly covered my arse, yes, I would say look seriously at implementing the idea ;)

Insert Into? Delete? Update?

Insert Into? Delete? Update?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment