Re: Search Across unrelated forms.

All sites should be in the same table. You can use a Yes/No field to
determine whether is is CDM or NonCDM. You could use the same form for both
and apply a fiter when you only want to see one or the other. The same would
be true for Macro or Micro and the Upgrade Type, Roll Out or Upgrade.

Since a site can have multiple B-End sites and a B-End site links multiple
sites, you will need two tables for that because that is a many to many
situation and needs a joining table. First would be a table that defines a
B-End site, but does not reference any specific site. The a table that joins
the B-End sites with the sites.
Site is one to many with B-End/Site table.

As to your programmes, One of the objectives to normalize a database is to
eliminate or minimize redundant data. A typical way to do this when you have
a recurring list of things like programmes; rather than include the text
description of the programme in each upgrade record, create a table that has
a primary key and the description of the programme. Then in the upgrade
table records, carry the primary key value of the related programme rather
than the text description. This table then becomes a child table to the
upgrade table. This accomplishes a couple of things. First, it reduced data
base size and if the description of the programme changes at any time, then
it is not necessary to find and replace it in every record in the upgrade
table. You only need to change it in the programme table.
B-End site is one to many with B-End/Site table
The B-End/Site table would need:
BEND_SITE_ID Autonumber - Primary Key
BEND_ID Long - Foreign Key - Contains the primary key of the record
in the
B-End table it relates to
SITE_ID Long - Foreign Key - Contains the primary key of the record
in the
Site table it relates to

For the Upgrades, you should have an Upgrade table. If I understand
correctly, an Upgrade is unique to a site. This table should be a child
table to the Site table and have a field that contains the primary key value
of the site record it is associated with.

I hope my response is a clear an complete as your description. If you have
further questions, please post back.


"RemySS" wrote:

> Hi Klatuu/TC,
>
> I have written out a description for you, hope this is clear enough!
>
> My role is to liaise with contractors who carry out work on communication
> sites. I need the database to make my role of recording site activities,
> invoicing, and searching for site progress easier and much less time
> consuming. An improvement in Data input errors are also and obvious
> requirement.
>
> Each site (a macro or microcell site) is given a 3 letter prefix and then 4
> numbers to identify it, e.g. HER 0123 = Herefordshire 0123. The county is
> also and indication of the region (North, south, or GLN (greater London)).
>
> There are 2 types of site –
>
> Rollouts (sites which are in the planning stages of being built or are
> currently undergoing construction)
>
> Upgrades (sites that are built and have different works carried out on them,
> i.e. installing new/replacing old technology.
>
> There are also two types of upgrade stage -
>
> 1. CDM – there is one principal contractor working on site and all other
> contractors carrying out work with the principal contractor are under its
> control.
>
> 2. Non-CDM – means that all contractors carrying out work on the site are
> independent of each other.
>
> An upgrade type is exactly that – the type of upgrade that is being carried
> out on-site. There is one upgrade number per upgrade type. The upgrade number
> is the single most unique field – there are no duplicates. A site can have
> many upgrade numbers, but an upgrade number can only be for one site. Upgrade
> types can occur on different sites and with different upgrade numbers. A Site
> can have up to 3 upgrade numbers at a time. Phase is either 2G Or 3G – this
> can be different for two records of the same site. Most of the other field
> (listed below) are the stages and different actions that have to be
> completed. This varies from programme to programme.
>
> A site in tables Non-CDM, CDMable, Micro Cell Upgrade, and Rollout can have
> what is called a B-End site too. This is a site which is a link between
> different sites. A site can have many B-End sites, and each B-End site has a
> upgrade number which is unique to that site (no duplicates anywhere at all,
> like the upgrade number).
>
> List of programmes:
> Edge Upgrades (no rollouts)
> Campbell’s Microwave (RO+UG)
> Eve’s Programme (RO+UG)
> Nokia NDB Deployment (RO+UG)
> LSE Base UG (UG)
> Base midland UG (UG)
> Alcatel (RO+UG)
> South NDB C&I (RO+UG)
> Nokia NDB North (RO+UG)
>
> Forms I Currently have (I refer to them as trackers):
> Edge
> Non-CDM Upgrades
> CDMable Upgrades
> Rollout
> Microcell Rollout
> Microcell upgrades
>
> Every Tracker has (with exceptions):
> SiteRef
> Region
> Site Name
> Upgrade Number (Not Rollout Trackers)
> Upgrade Type (Not Rollout Trackers)
> Phase
> Scenario
>
> Edge Tracker also contains:
> 1st I&C Contractor
> RR Issued to 1st I&C
> Schedule E Received
> Date Schedule E Received
> Payment Milestone
> Comments
>
> Non-CDM Tracker also contains:
> De-Install (Yes/No)
> Programme (Text)
> 1st I&C Contractor (Text)
> RR Issued to 1st I&C (Text)
> Schedule E Received (Text)
> Payment Milestone (Date/Time)
> U3 (Text)
> U410 (Text)
> U6 (Text)
> 2nd IandC Contractor (Text)
> RR Issued to 2 IandC (Text)
> Schedule E Received from 2 IandC (Text)
> B End SiteRef (Text)
> B End Third Party Site (Text)
> Link Ref Number (Number)
> 3 IandC Contractor (Text)
> RR Issued to 3 IandC (Text)
> Schedule E Received from 3 IandC (Text)
> 4 IandC Contractor (Text)
> RR Issued to 4 IandC (Text)
> Schedule E Received from 4 IandC (Text)
> Comments (Text)
>
> CDMable Tracker also contains:
> Upgrade Number2
> Upgrade Number3
> Upgrade Type2
> Upgrade Type3
> Third Party Site
> Principal Contractor
> Designer
> DC/AD
> B End SiteRef
> B End Third Party Site
> Link Ref Number
> Request 1 for DRA/Drawings
> Request 2 for DRA/Drawings
> U3: GA Drawing Received/Reviewed
> U3: Designers Hazard Ass/RR Received
> U3: Initial F10 Issued to HSE
> U410: Initial F10 & PTHSP Issued to D&C,1 Request for CDHSP Plan
> D&C Completed F10 issued to HSE
> Second Request for CPHSP
> PC's CPHSP Received and Approved
> U6: Stage 1 Payment Milestone
> Request 1 for H&S File
> Request 2 for H&S File
> D&C Sched E Provided
> H&S File Compiled and Issued
> U8: Stage 2 Payement Milestone
> 1st I&C Contractor
> U8: RR Issued to I&C
> U10: Shed E Provided from I&C
> I&C Contractor Name
> U8: RR Issued to I&C-2
> U10: Shed E Provided from I&C-2
> IMS Sched E Received and H&S File Updated
> U10: SMC Payment Milestone
> Comments
>
> Rollout Tracker also contains:
> Principal Contractor
> Designer
> DCorAD
> Request 1 for Design Info
> Request 2 for Design Info
> U3 GA Drawings RR
> MS6 Designers Hazard Ass R
> MS6 Initial F10 Issued to HSE
> MS13 Initial F10 PTHSP Issued
> MS13 Payment Milestone
> D&C Completed F10 Issued to HSE
> Request 2 for CPHSP Plan
> MS12 PCs CPHSP Approved
> Request 1 for H&S file
> Request 2 for H&S file
> D&C Sched E Provided
> H&S File Compiled and Issued
> MS16 Stage 2 Payement Milestone
> I&C Contractor Name
> MS16 RR Issued to I&C
> MS23 Shed E Provided from I&C-2
> IMS Sched E Received and H&S File Updated
> MS23 SMC Payment Milestone
> B End SiteRef
> B End Third Party Site
> Link Ref Number
> Comments
>
> Micro Cell Rollout also contains:
> Contractor
> Designer
> Programme
> DCorAD
> Request 1 for Design Info
> Request 2 for Design Info
> Sched E DRA Received
> Request 1 Method Statement
> Request 2 Method Statement
> Site Spec Method Statement Received
> Updated Sched E Forwarded to 2 Contractor
> Returned Sched E Received
> Handover Pack Approved and IMS Updated
> Payment Milestone
> Comments
> Request 1 for Design InfoCopy
> Request 2 for Design InfoCopy
> U3 GA Drawings RR
> MS6 Designers Hazard Ass R
> MS6 Initial F10 Issued to HSE
> MS13 Initial F10 PTHSP Issued
> MS13 Payment Milestone
>
> Micro Cell Upgrade also contains:
> Third Party Site
> 1st I&C Contractor
> RR Issued to 1st I&C
> Programme
> Schedule E Received
> Payment Milestone
> U3
> U410
> U6
> 2nd IandC Contractor
> RR Issued to 2 IandC
> Schedule E Received from 2 IandC
> B End SiteRef
> B End Third Party Site
> Link Ref Number
> 3 IandC Contractor
> RR Issued to 3 IandC
> Schedule E Received from 3 IandC
> 4 IandC Contractor
> RR Issued to 4 IandC
> Schedule E Received from 4 IandC
> Comments
>
> The tables are the same, so I’m guessing duplicate data will occur.
>
> I know its probably simple to normalize the tables and set the relationships
> so I don’t get duplicate/redundant records, but I seem to be doing it wrong –
> I would really appreciate a heads up on how I would go about this. If at all
> possible, I would like to keep the form designs the same, and then just add
> controls again if I had to.
>
> Thanks in advance,
>
>
>
> "Klatuu" wrote:
>
> > Post back to this thread so I will know it is here. I doubt you will have to
> > get rid of all your objects. Probably just need to modify them.
> >
> > "RemySS" wrote:
> >
> > > TC/Klatuu,
> > >
> > > Okay, what i will do is spend some time writing a description of how it
> > > should be and post that with some more info. will i be able to keep the forms
> > > or do i have to redo them all? i also have reports and queries that i would
> > > like to keep, all with VBA code too.
> > >
> > > Thanks for the help so far guys.
> > >
> > > "Klatuu" wrote:
> > >
> > > > RemySS,
> > > >
> > > > I don't like to post my E-mail address. I have received a ton of junk mail
> > > > from having done so previously. It may not be necessary to post all the
> > > > fields. A description of what each table is used for and what differentiates
> > > > them would do for now. What I suspect, based on your table names, is that
> > > > they are mostly the same with some minor differences. For example,
> > > > South_CDMable and South_Non-CDM look like they are probably identical and
> > > > really only need an identifying field to define whether it is CDMable or
> > > > Non-CMD.
> > > >
> > > > "RemySS" wrote:
> > > >
> > > > > Hi TC/Klatuu,
> > > > >
> > > > > You are both right! - the reason i want to do a search like this is because
> > > > > I havent set up my table structure correctly and have found problems. Not
> > > > > being an advanced database person, I found it too complicated to normalize
> > > > > and creating relationships caused an infinate number of errors. I have a long
> > > > > list of field names, too many to list - but i could email you them to another
> > > > > address if thats possible.
> > > > >
> > > > > The table names are the same as the form names - i created them directly
> > > > > from the tables due to my aformentioned problems. I would like to remedy this
> > > > > though so long as i dont have to completely re-create the entire database.
> > > > >
> > > > > Table names:
> > > > > Edge_Tracker
> > > > > South_CDMable
> > > > > South_Non-CDM
> > > > > South_Rollout
> > > > > Microcell_Upgrade
> > > > > Microcell_RO
> > > > >
> > > > > I know that by having my database thee way it is now, i will get lots of
> > > > > redundant and duplicate adat, but i couldnt find a way to normalize and
> > > > > create the right relationships - too complicated!
> > > > >
> > > > > If i can send either of u a copy of the list, please supply me an email.
> > > > >
> > > > > Thanks!
> > > > >
> > > > > "TC" wrote:
> > > > >
> > > > > > It is very unusual that you would want to do the same search across
> > > > > > multiple tables, in a properly designed relational database. I think
> > > > > > you may have designed your tables to match the forms! This is generally
> > > > > > not correct. The tables should be designed to reflect the logical
> > > > > > structure of the information, regardless of how that information is
.

Relevant Pages

  • Re: Buying MS Office on Ebay
    ... Hi Milly- As a residential construction contractor I have become quite adept ... > purchase a product and then use that product to upgrade to a newer ... I am looking on Ebay ... If it will not install ...
    (microsoft.public.office.misc)
  • Re: Search Across unrelated forms.
    ... There are also two types of upgrade stage - ... there is one principal contractor working on site and all other ... RR Issued to 2 IandC ... Request 1 for DRA/Drawings ...
    (microsoft.public.access.modulesdaovba)
  • wire size and 200amp service
    ... I am doing a significant kitchen upgrade and the contractor has ... electricians out to quote on the work---and received 3 very ... requires 4/0 aluminum wiring for 200amp service. ...
    (alt.home.repair)
  • Re: adding client licenses
    ... The contractor since went out of business. ... >You can upgrade if you want without any issues... ... >very small fee. ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: Question on Callsign after FCC license upgrade (USA)
    ... upgrade from General to Extra. ... application stating that I wanted to "Request to change a station call ... I remain with my over ten year old Callsign from ... that if I now make a request for a systematic callsign change online, ...
    (rec.radio.amateur.misc)