Duplicating field entry from one table to another (1 Viewer)

DrewB

New member
Local time
Today, 08:01
Joined
Sep 20, 2017
Messages
3
hello All

I'm new here so please be kind if this sounds like garbage.

I am a *very* new access user - basically a one off as I have been put in a position where I need to use the system.

I have my database set up and an input form made, but I need some advice on how to do the following:

I need to create a gantt chart of types (using Qlikview) but in order for it to work I need to have some new data in my database. I have to have "phases" for each project. I don't think there is a sensible way to add this to my main data table, which is basically a list of each company, non duplicated, with associated data - (duplication would be needed for each phase but I think this would screw everything up I have already done).

What I believe is the best thing to do is to create a new table where each company is duplicated three times (for each phase) and then do a lookup of some sort to my main table to get the dates and use this to produce my chart.

My main question is - is it possible for this new table to lookup and automatically populate three new lines when a new company is added, so that the dates can be populated?

This may be over complicated and there is a better way to do this but I am not sure what that would be.

any help would be very much appreciated.:banghead::banghead::banghead:

Thanks
Drew
 

Minty

AWF VIP
Local time
Today, 15:01
Joined
Jul 26, 2013
Messages
10,355
You would normally store this type of data in a vertical fashion and assuming the phases could vary I would be tempted to use dates rather than an arbitrary "phase number".

So something like, (and this assumes you have a list of projects with the companyID already stored per project, or can more than one company work on a project??)
Code:
[B][I]
tblPhases[/I][/B]
PhaseID       - Autonumber
ProjectID     - Number Foriegn Key to Project Table
PhaseStart       - Date
PhaseProjectedEnd      - Date
PhaseActualEnd     -  Date
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2002
Messages
42,985
Be very careful about creating limits when none really exist nor should they. You have "3" phases now. What if the business model changes slightly and now you need 4? Are you prepared to change the tables queries and reports already built? If you don't make an arbitrary limit in the first place, you won't have to recover from that decision in the future. This is the thinking you need to be doing when you are designing a database that is properly normalized.

Your thinking that you need a new table is correct. However it doesn't actually duplicate company data. Project data stays where it is but you need to create two new tables. The first will be phases. This table defines the current three phases so it will only contain three rows. In the future, if you need to add additional phases, you will add new rows to this table and that should be the extent of the changes required. The second new table is ProjectPhase. This is a junction table that relates projects to phases. This table contains columns that are specific to a phase such as start date.

When you add a new project, there is no need to auto-populate the phases table. Each phase should be added as it is started so that the ProjectPhase table contains only active or completed phases. It will not contain proposed or future phases that may never happen.

To handle a join (not a lookup) between two tables when one of them may not contain any relevant rows, you use a left join. So a query that joins projects to ProjectPhases will select all rows from projects and any rows from ProjectPhases that exist. If you were to use an inner join, you would only get rows where a project actually had rows in the phases table. Here's an analogy. People have children but not ALL people have children. Sometimes you need to find people who have NO children. Sometimes you need to find all people and include their children if they have any.

Do some reading on normalization and join types. You should find a picture of a group of Ven diagrams that show how all the join types work.
 

Attachments

  • VenDiagrams.JPG
    VenDiagrams.JPG
    57.7 KB · Views: 83

DrewB

New member
Local time
Today, 08:01
Joined
Sep 20, 2017
Messages
3
Hi Both

Thank you both very much for your helpful responses.

turns out I got around this due to the fact they aren't really Projects in the conventional sense and I wasn't building a true gantt chart.

All this information is very useful for me going forward though so thanks again :)

Drew
 

Users who are viewing this thread

Top Bottom