Re: [MS_AccessPros] Database design - best practice

 

Hi Duane

Thanks for your thoughts.  I hadn't thought of creating a database to manage the project but I like the idea of linking to other, more specialist types of files (Word, Excel, MS Project etc).  I'll experiment - thanks for the prompt.

Best regards

Ray 

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I'm not sure Access alone can/should handle all of these requirements. I would suggest as just a kick start for discussion three tables:

 tblProjects

 tblTasks

 tblDetails

Projects is the top level with Tasks being a child of projects and Details being a child of Tasks. Each of these tables would have fields like:

Title

Description

ExpectedDate

CompletionDate

PercentComplete

ResponsiblePerson

Sequence

This could probably managed in a single table with parent and child IDs. At the project level there could be links to project documents and project notes.

You might also want to incorporate Word docs, MS Project or Planner as well as other tools.

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of rayfrew@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 26, 2018 4:45 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Database design - best practice

 

Hi Everyone.  

I'd appreciate your thoughts and pointers about how to design, track development and achieve customer acceptance of a database (Access being the preferred route).

I would have a workflow of:

1. Discuss what the customer wants - this gets you Quality expectations, Acceptance criteria and major requirements.

2. Break the major requirements into smaller requirements which can be prioritised and estimated.

3. Create a plan for the tasks (design db, design style, build tables, queries, forms, test, and no doubt change)

4. Cope with change (oh, by the way, can you just...)  (I don't like it ....)

What I'm interested in is techniques and/or tools to manage / track the above.  Most of the books I've read about Access go into the techniques of how to use Access but don't mention the User / Business reasons for having a database in the first place.  Any hints, tips, websites, books, forums, ideas welcomed.

Many thanks

Ray

__._,_.___

---

Posted by: rayfrew@gmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___

Re: [MS_AccessPros] Database design - best practice

 

I'm not sure Access alone can/should handle all of these requirements. I would suggest as just a kick start for discussion three tables:

 tblProjects

 tblTasks

 tblDetails

Projects is the top level with Tasks being a child of projects and Details being a child of Tasks. Each of these tables would have fields like:

Title

Description

ExpectedDate

CompletionDate

PercentComplete

ResponsiblePerson

Sequence

This could probably managed in a single table with parent and child IDs. At the project level there could be links to project documents and project notes.

You might also want to incorporate Word docs, MS Project or Planner as well as other tools.

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of rayfrew@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 26, 2018 4:45 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Database design - best practice

 

Hi Everyone.  

I'd appreciate your thoughts and pointers about how to design, track development and achieve customer acceptance of a database (Access being the preferred route).

I would have a workflow of:

1. Discuss what the customer wants - this gets you Quality expectations, Acceptance criteria and major requirements.

2. Break the major requirements into smaller requirements which can be prioritised and estimated.

3. Create a plan for the tasks (design db, design style, build tables, queries, forms, test, and no doubt change)

4. Cope with change (oh, by the way, can you just...)  (I don't like it ....)

What I'm interested in is techniques and/or tools to manage / track the above.  Most of the books I've read about Access go into the techniques of how to use Access but don't mention the User / Business reasons for having a database in the first place.  Any hints, tips, websites, books, forums, ideas welcomed.

Many thanks

Ray

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___

[MS_AccessPros] Database design - best practice

 

Hi Everyone.  

I'd appreciate your thoughts and pointers about how to design, track development and achieve customer acceptance of a database (Access being the preferred route).

I would have a workflow of:

1. Discuss what the customer wants - this gets you Quality expectations, Acceptance criteria and major requirements.

2. Break the major requirements into smaller requirements which can be prioritised and estimated.

3. Create a plan for the tasks (design db, design style, build tables, queries, forms, test, and no doubt change)

4. Cope with change (oh, by the way, can you just...)  (I don't like it ....)

What I'm interested in is techniques and/or tools to manage / track the above.  Most of the books I've read about Access go into the techniques of how to use Access but don't mention the User / Business reasons for having a database in the first place.  Any hints, tips, websites, books, forums, ideas welcomed.

Many thanks

Ray

__._,_.___

---

Posted by: rayfrew@gmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___

Re: [MS_AccessPros] Need to stop duplicate records

 

That worked great. That was amazing. Thank you very much!!!!

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On ‎Friday‎, ‎September‎ ‎21‎, ‎2018‎ ‎04‎:‎26‎:‎55‎ ‎PM‎ ‎CDT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Art,

Did you try what I suggested?

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, September 21, 2018 3:53 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Need to stop duplicate records

 

PK for tblApplication is ApplicationID

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On ‎Friday‎, ‎September‎ ‎21‎, ‎2018‎ ‎03‎:‎18‎:‎13‎ ‎PM‎ ‎CDT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hi Art,

I would try something using a subquery in the criteria. It would be a little simpler if you could provide the primary key field of tblApplication. This query assumes the Application Received date and time are both populated.

SELECT qryApplicantExtended.ApplicantID,

 qryApplicantExtended.[Applicant Name],

 tlkpApplicationType.ApplicationTypeName,

 qryApplicantExtended.HomePhone,

 tlkpGender.GenderName,

 qryApplicantExtended.MobilePhone,

 qryApplicantExtended.WorkPhone,

 tblApplication.AppReceivedDate,

 tblApplication.AppReceivedTime

FROM ((qryApplicantExtended

 INNER JOIN tlkpApplicationType ON qryApplicantExtended.ApplicationTypeID = tlkpApplicationType.ApplicationTypeID)

 INNER JOIN tlkpGender ON qryApplicantExtended.GenderID = tlkpGender.GenderID)

 LEFT JOIN tblApplication ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID

WHERE qryApplicantExtended.ApplicantStatusTypeID=4

 AND AppReceivedDate + AppReceivedTime =

 (SELECT TOP 1 AppReceivedDate + AppReceivedTime

  FROM tblApplication A 

  WHERE A.ApplicantID = tblApplication.ApplicantID 

  ORDER BY AppReceivedDate Desc, AppReceivedTime DESC)

ORDER BY qryApplicantExtended.LastName;

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, September 21, 2018 1:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Need to stop duplicate records

 

I have the following query:

SELECT qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name], tlkpApplicationType.ApplicationTypeName, qryApplicantExtended.HomePhone, tlkpGender.GenderName, qryApplicantExtended.MobilePhone, qryApplicantExtended.WorkPhone, tblApplication.AppReceivedDate, tblApplication.AppReceivedTime
FROM ((qryApplicantExtended INNER JOIN tlkpApplicationType ON qryApplicantExtended.ApplicationTypeID = tlkpApplicationType.ApplicationTypeID) INNER JOIN tlkpGender ON qryApplicantExtended.GenderID = tlkpGender.GenderID) LEFT JOIN tblApplication ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID
WHERE (((qryApplicantExtended.ApplicantStatusTypeID)=4))
ORDER BY qryApplicantExtended.LastName;

Which is returning duplicate applicants. I understand why its happening. It because and applicant can have more then one application. How can I get it to return the latest application which would eliminate duplicate?

Thank you 

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: Art Lorenzini <dbalorenzini@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___

Re: [MS_AccessPros] Need to stop duplicate records

 

Art,

Did you try what I suggested?

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, September 21, 2018 3:53 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Need to stop duplicate records

 

PK for tblApplication is ApplicationID

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Valar Dohaeris"

On ‎Friday‎, ‎September‎ ‎21‎, ‎2018‎ ‎03‎:‎18‎:‎13‎ ‎PM‎ ‎CDT, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hi Art,

I would try something using a subquery in the criteria. It would be a little simpler if you could provide the primary key field of tblApplication. This query assumes the Application Received date and time are both populated.

SELECT qryApplicantExtended.ApplicantID,

 qryApplicantExtended.[Applicant Name],

 tlkpApplicationType.ApplicationTypeName,

 qryApplicantExtended.HomePhone,

 tlkpGender.GenderName,

 qryApplicantExtended.MobilePhone,

 qryApplicantExtended.WorkPhone,

 tblApplication.AppReceivedDate,

 tblApplication.AppReceivedTime

FROM ((qryApplicantExtended

 INNER JOIN tlkpApplicationType ON qryApplicantExtended.ApplicationTypeID = tlkpApplicationType.ApplicationTypeID)

 INNER JOIN tlkpGender ON qryApplicantExtended.GenderID = tlkpGender.GenderID)

 LEFT JOIN tblApplication ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID

WHERE qryApplicantExtended.ApplicantStatusTypeID=4

 AND AppReceivedDate + AppReceivedTime =

 (SELECT TOP 1 AppReceivedDate + AppReceivedTime

  FROM tblApplication A 

  WHERE A.ApplicantID = tblApplication.ApplicantID 

  ORDER BY AppReceivedDate Desc, AppReceivedTime DESC)

ORDER BY qryApplicantExtended.LastName;

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, September 21, 2018 1:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Need to stop duplicate records

 

I have the following query:

SELECT qryApplicantExtended.ApplicantID, qryApplicantExtended.[Applicant Name], tlkpApplicationType.ApplicationTypeName, qryApplicantExtended.HomePhone, tlkpGender.GenderName, qryApplicantExtended.MobilePhone, qryApplicantExtended.WorkPhone, tblApplication.AppReceivedDate, tblApplication.AppReceivedTime
FROM ((qryApplicantExtended INNER JOIN tlkpApplicationType ON qryApplicantExtended.ApplicationTypeID = tlkpApplicationType.ApplicationTypeID) INNER JOIN tlkpGender ON qryApplicantExtended.GenderID = tlkpGender.GenderID) LEFT JOIN tblApplication ON qryApplicantExtended.ApplicantID = tblApplication.ApplicantID
WHERE (((qryApplicantExtended.ApplicantStatusTypeID)=4))
ORDER BY qryApplicantExtended.LastName;

Which is returning duplicate applicants. I understand why its happening. It because and applicant can have more then one application. How can I get it to return the latest application which would eliminate duplicate?

Thank you 

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___