Re: 2 identical table designs

Tech-Archive recommends: Fix windows errors by optimizing your registry

Why must it be kept separate? Is it kept in a different physical location,
if it is you can ignore the following. Assuming the reason for the different
physical location is logical or down to some "policy" decision.

Keep it all in the same table, add a field to indicate whether the record is
an "original" or an "adjustment".

If there is a natural primary key (say an index based on both Company Code
and Account) then use it instead of the "id stuff".

If the combination of Company Code and Account are currently unique in both
the current tables then the addition of the "original"/"adjustment" marker
to the primary key will create the necessary primary key.

With this construct you can now query on just one table and use the marker
to differentiate from "original" and "adjustment" data.

BTW If there is no natural key you could stick with the "id stuff" and just
use the marker as mentioned above.

--
Slainte

Craig Alexander Morrison
"Danny" <Danny@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71E00D4A-5167-475E-B4D3-BAE104B26B6E@xxxxxxxxxxxxxxxx
>I have 2 tables with the same numbers of fields and same name
> Table 1 is the original data and
> Table 2 is the adjustments data, which needs to be kept seperate
>
> I am having trouble combining data from both tables. In some instances
> there
> is data in table 2 but not in table one
>
> eg account number may be in t2 not not in t1
> I have considered using a junction table but this does not work
> Other than the ID in both tables there are no other primary keys
>
> Fields are
>
> ID
> Company Code (duplicate YES)
> Account (duplictaes YES)
> Value 1
> value 2
> Text
>
> Can some help and suggest ways of improving or meths that I can use to
> return data say based on both Company code and account numbers.
>
> Regards
> Danny
>







.

Relevant Pages

  • RE: concatenation of a field value and a counter
    ... company code I was referring to before is not the primary key, ... FROM tblCompany INNER JOIN tblJob ON tblCompany.Company_ID = tblJob.Company_ID ... company code would be the primary key of the company table and a foreign key ... if you want to concatenate them in the format you describe: ...
    (microsoft.public.access.formscoding)
  • Re: Table Design - Relationships
    ... how I design the forms, I always get errors, e.g. "Forms not updatable" or ... Can a facility have a bill without an account? ... > FacilityID 'Autonumber - Primary Key ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Distinct insert
    ... however at the same time inserting a unique primary key into the same ... Primary Key From Table 1, Account Number ... I need to put that information in a seperate table that is similair to this: ...
    (microsoft.public.sqlserver.mseq)
  • Re: Question Regarding Date Sensitive Tables
    ... Account Number ... Account No., (Joint Primary Key) ... Bill Date, ... Long Distance Charges for this cell phone, ...
    (microsoft.public.access.forms)
  • Re: Table Design - Relationships
    ... > between tblAccount and tblBills. ... Can a facility have a bill without an account? ... FacilityID 'Autonumber - Primary Key ...
    (microsoft.public.access.tablesdbdesign)