Populate data based on another table

14,534

Expert Mod 8TB

What is the metadata of these two tables?

Here is an example of how to post table MetaData :
Table Name=tblBookings

Expand|Select|Wrap|Line Numbers

 Field; Type; IndexInfo
 Field; Type; IndexInfo

StudentID; Autonumber; PK

Family; String; FK

Name; String

University; String; FK

Mark; Numeric

LastAttendance; Date/Time

Mar 28 '07 #2

What is the metadata of these two tables?

Here is an example of how to post table MetaData :
Table Name=tblBookings

Expand|Select|Wrap|Line Numbers

Field; Type; IndexInfo

Field; Type; IndexInfo

StudentID; Autonumber; PK

Family; String; FK

Name; String

University; String; FK

Mark; Numeric

LastAttendance; Date/Time

Thank you for your post, I used documenter to get the metadata, I deleted some fields to keep my post as small as possible.

Expand|Select|Wrap|Line Numbers

 Table: TBLCustomers    Page: 1

    Properties

    DateCreated:     05/03/2007 09:23:59 a.m.    DefaultView:     Datasheet

    GUID:     {guid {B8EC568C-0FC8-4A3D-    LastUpdated:     14/03/2007 09:56:18 a.m.

    9CF4-0EBB7229B7A4}}

    NameMap:     Long binary data    OrderByOn:     False

    Orientation:     Left-to-Right    RecordCount:     222

    Updatable:     True

    Columns

    Name    Type    Size

    CustomerNumber    Long Integer    4

    AllowZeroLength:     False

    Attributes:     Fixed Size, Auto-Increment

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     1

    ColumnWidth:     Default

    DataUpdatable:     False

    GUID:     {guid {EFB6B257-80C9-42A1-8088-62B4D39F808B}}

    OrdinalPosition:     0

    Required:     False

    SourceField:     CustomerNumber

    SourceTable:     TBLCustomers

    CustomerName    Text    50

    AllowZeroLength:     True

    Attributes:     Variable Length

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     2988

    DataUpdatable:     False

    Description:     CustomerName 

    DisplayControl:     Text Box

    Format:     >

    GUID:     {guid {594FAD97-5D26-483C-82A5-D8B29347C4D6}}

    IMEMode:     0

    IMESentenceMode:     3

    OrdinalPosition:     1

    Required:     False

    SourceField:     CustomerName

    SourceTable:     TBLCustomers

    UnicodeCompression:     True

    AgentID    Long Integer    4

    AllowZeroLength:     False

    Attributes:     Fixed Size

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     Default

    DataUpdatable:     False

    DecimalPlaces:     Auto

    Description:     Asentid Number

    DisplayControl:     Text Box

    GUID:     {guid {435CFD96-A99B-42B5-9855-B8D2447184B0}}

    OrdinalPosition:     7

    Required:     False

    SourceField:     AgentID

    SourceTable:     TBLCustomers

    Relationships

    TBLAgentsTBLCustomers

    TBLAgents    TBLCustomers

    AgentID    AgentID

    Attributes:     Not Enforced

    RelationshipType:     One-To-Many

    TBLCustomersTBLOrdersHeaders

    TBLCustomers    TBLOrdersHeaders

    CustomerNumber    CustomerNumber

    Attributes:     Not Enforced

    RelationshipType:     One-To-Many

    Table Indexes

    Name    Number of Fields

    CustomersAgentID    1

    Clustered:     False

    DistinctCount:     4

    Foreign:     False

    IgnoreNulls:     False

    Name:     CustomersAgentID

    Primary:     False

    Required:     False

    Unique:     False

    Fields:

    AgentID    Ascending

    Number    1

    Clustered:     False

    DistinctCount:     223

    Foreign:     False

    IgnoreNulls:     False

    Name:     Number

    Primary:     False

    Required:     False

    Unique:     True

    Fields:

    CustomerNumber    Ascending

    PrimaryKey    1

    Clustered:     False

    DistinctCount:     223

    Foreign:     False

    IgnoreNulls:     False

    Name:     PrimaryKey

    Primary:     True

    Required:     True

    Unique:     True

    Fields:

    CustomerNumber    Ascending
 
Table: TBLOrdersHeaders    

    Properties

    DateCreated:     15/03/2007 12:41:51 p.m.    DefaultView:     Datasheet

    GUID:     {guid {8954442E-201D-4E59-    LastUpdated:     28/03/2007 09:00:50 a.m.

    84CA-6639F7F51EA9}}

    NameMap:     Long binary data    OrderByOn:     False

    Orientation:     Left-to-Right    RecordCount:     3357

    Updatable:     True

    Columns

    Name    Type    Size

    OrderDate    Date/Time    8

    AllowZeroLength:     False

    Attributes:     Fixed Size

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     Default

    DataUpdatable:     False

    DefaultValue:     =Now()

    Description:     Order Date

    Format:     Short Date

    GUID:     {guid {04E584D1-0383-4937-8103-9245926680E3}}

    IMEMode:     0

    IMESentenceMode:     3

    OrdinalPosition:     0

    Required:     True

    SourceField:     OrderDate

    SourceTable:     TBLOrdersHeaders

    NumberOrder    Long Integer    4

    AllowZeroLength:     False

    Attributes:     Fixed Size, Auto-Increment

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     Default

    DataUpdatable:     False

    Description:     Number de la Order

    GUID:     {guid {55022CF9-DFB9-456B-8B01-85C17D093167}}

    OrdinalPosition:     1

    Required:     False

    SourceField:     NumberOrder

    SourceTable:     TBLOrdersHeaders

    CustomerNumber    Long Integer    4

    AllowZeroLength:     False

    Attributes:     Fixed Size

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     Default

    DataUpdatable:     False

    DecimalPlaces:     Auto

    Description:     Customer Number 

    DisplayControl:     Text Box

    GUID:     {guid {72EF6728-1B7A-4821-8535-634FB3507CA4}}

    OrdinalPosition:     2

    Required:     False

    SourceField:     CustomerNumber

    SourceTable:     TBLOrdersHeaders

    AgentID    Long Integer    4

    AllowZeroLength:     False

    Attributes:     Fixed Size

    CollatingOrder:     English

    ColumnHidden:     False

    ColumnOrder:     Default

    ColumnWidth:     Default

    DataUpdatable:     False

    DecimalPlaces:     Auto

    Description:     Agent ID

    DisplayControl:     Text Box

    GUID:     {guid {6F015A46-DA9B-4712-9CFA-E8D3ACA0A136}}

    OrdinalPosition:     3

    Required:     False

    SourceField:     AgentID

    SourceTable:     TBLOrdersHeaders

    Relationships

    TBLAgentsTBLOrdersHeaders

    TBLAgents    TBLOrdersHeaders

    AgentID    AgentID

    Attributes:     Not Enforced

    RelationshipType:     One-To-Many

    TBLCustomersTBLOrdersHeaders

    TBLCustomers    TBLOrdersHeaders

    CustomerNumber    CustomerNumber

    Attributes:     Not Enforced

    RelationshipType:     One-To-Many

    Table Indexes

    Name    Number of Fields

    AgentID    1

    Clustered:     False

    DistinctCount:     1

    Foreign:     False

    IgnoreNulls:     False

    Name:     AgentID

    Primary:     False

    Required:     False

    Unique:     False

    Fields:

    AgentID    Ascending

    CustomerNumber    1

    Clustered:     False

    DistinctCount:     228

    Foreign:     False

    IgnoreNulls:     False

    Name:     CustomerNumber

    Primary:     False

    Required:     False

    Unique:     False

    Fields:

    CustomerNumber    Ascending

    NumberOrder    1

    Clustered:     False

    DistinctCount:     3357

    Foreign:     False

    IgnoreNulls:     False

    Name:     NumberOrder

    Primary:     False

    Required:     False

    Unique:     True

    Fields:

    NumberOrder    Ascending

    PrimaryKey    1

    Clustered:     False

    DistinctCount:     3357

    Foreign:     False

    IgnoreNulls:     False

    Name:     PrimaryKey

    Primary:     True

    Required:     True

    Unique:     True

    Fields:

    NumberOrder    Ascending

Mar 28 '07 #3

14,534

Expert Mod 8TB

That was a little too long for most experts to trawl through. Is this an accurate representation of the table structures.

Table Name=TBLCustomers

Expand|Select|Wrap|Line Numbers

  Field; Type; IndexInfo

CustomerNumber; Long Integer; PK and FK (to PK of TBLOrdersHeaders)

CustomerName; String; 

AgentID; Long Integer; FK (to PK of TBLAgents)

CustomersAgentID;  Long Integer; FK (to PK of TBLCustomersAgents)

Table Name=TBLOrdersHeaders

Expand|Select|Wrap|Line Numbers

  Field; Type; IndexInfo

NumberOrder; LongInteger; PK    

OrderDate; Date/Time

CustomerNumber; Long Integer; FK (to PK of TBLOrdersHeaders)

AgentID; Long Integer; FK (to PK of TBLAgents)

Mar 29 '07 #4

That was a little too long for most experts to trawl through. Is this an accurate representation of the table structures.

Table Name=TBLCustomers

Expand|Select|Wrap|Line Numbers

Field; Type; IndexInfo

CustomerNumber; Long Integer; PK and FK (to PK of TBLOrdersHeaders)

CustomerName; String;

AgentID; Long Integer; FK (to PK of TBLAgents)

CustomersAgentID; Long Integer; FK (to PK of TBLCustomersAgents)

Table Name=TBLOrdersHeaders

Expand|Select|Wrap|Line Numbers

Field; Type; IndexInfo

NumberOrder; LongInteger; PK

OrderDate; Date/Time

CustomerNumber; Long Integer; FK (to PK of TBLOrdersHeaders)

AgentID; Long Integer; FK (to PK of TBLAgents)

Yes, it is.

Thank you

Mar 29 '07 #5

14,534

Expert Mod 8TB

OK AgentID should not be in both of these tables because this is duplicate information. If a customer always has the same agent then leave it in the TBLCustomers. If the agent can change per order then leave it in TBLOrdersHeaders. But it should only be in one of these tables and not both of them.

Mary

Table Name=TBLCustomers

Expand|Select|Wrap|Line Numbers

  Field; Type; IndexInfo

CustomerNumber; Long Integer; PK

CustomerName; String; 

AgentID; Long Integer; FK (to PK of TBLAgents)

CustomersAgentID;  Long Integer; FK (to PK of TBLCustomersAgents)

Table Name=TBLOrdersHeaders

Expand|Select|Wrap|Line Numbers

  Field; Type; IndexInfo

NumberOrder; LongInteger; PK    

OrderDate; Date/Time

CustomerNumber; Long Integer; FK (to PK of TBLCustomers)

AgentID; Long Integer; FK (to PK of TBLAgents)

[/quote]

Mar 29 '07 #6

OK AgentID should not be in both of these tables because this is duplicate information. If a customer always has the same agent then leave it in the TBLCustomers. If the agent can change per order then leave it in TBLOrdersHeaders. But it should only be in one of these tables and not both of them.

Mary

Table Name=TBLCustomers

Expand|Select|Wrap|Line Numbers

Field; Type; IndexInfo

CustomerNumber; Long Integer; PK

CustomerName; String;

AgentID; Long Integer; FK (to PK of TBLAgents)

CustomersAgentID; Long Integer; FK (to PK of TBLCustomersAgents)

Table Name=TBLOrdersHeaders

Expand|Select|Wrap|Line Numbers

Field; Type; IndexInfo

NumberOrder; LongInteger; PK

OrderDate; Date/Time

CustomerNumber; Long Integer; FK (to PK of TBLCustomers)

AgentID; Long Integer; FK (to PK of TBLAgents)

[/quote]

Mary,

Thank you for your answer. I know the information is "duplicated" but as it is time critical, I need to record at order level who the agent was, because the customers table indicate the current situation, but I want to keep history at order level.

i.e. Today for customer A the agentID is 1, two months from now the agentID could be 2, but I want to keep the sales history for agent 1, I can't ignore previous sales

Thanks,

Mar 29 '07 #7

14,534

Expert Mod 8TB

When adding a record by form to TBLOrderHeaders, put the following in the After Update event of the CustomerNumber textbox:

Expand|Select|Wrap|Line Numbers

 
Private Sub CustomerNumber_AfterUpdate()
 
   Me!AgentID = DLook("[AgentID]", "TBLCustomers", "[CustomerNumber]=" & Me!CustomerNumber
 
End Sub

This assumes CustomerNumber is a Number fieldtype and not a text field.

Mary

Mar 29 '07 #8

bnickelsen

I Hate to but in, as I am no expert.

But is this not a case where you would want another table of agents. And one field in each of the other tables that point to the agents table?

Mar 29 '07 #9

14,534

Expert Mod 8TB

I Hate to but in, as I am no expert.

But is this not a case where you would want another table of agents. And one field in each of the other tables that point to the agents table?

Based on the metadata that was posted this is the case. There is a table called TBLAgents.

Never be afraid to butt in. That's how we all learn and you were right. :)

Mar 29 '07 #10

Based on the metadata that was posted this is the case. There is a table called TBLAgents.

Never be afraid to butt in. That's how we all learn and you were right. :)

Mary,

Thank you very much for your valuable help.

Mar 30 '07 #11

How to populate a DataSet schema with data from and XML string?

14,534

Expert Mod 8TB

Mary,

Thank you very much for your valuable help.

You're welcome!

Mar 30 '07 #12

by: alex | last post by:

Hi, I am looking for a way to populate an HTML table from an external local text file which looks like this: DATE/TIME LAT. LON. DEPTH. ML....

PHP

by: Sharon | last post by:

I have successfully loaded a DataSet object with a XML schema (XSD). Now I wish to populate the tables that was created in the DataSet. I have an XML file/string that contain all the needed data...

C# / C Sharp

populate a dinamic table with two different querys.

by: Chifo | last post by:

hello. i have a problem with a populate html table with data from table here it's the problem two querys retrieving data from table, one of querys show me a colletion of data from 6:00 am to...

PHP

How i can populate all fileds dynamically in jsp page based on contents found in xml file?

by: vj | last post by:

How i can populate all fileds dynamically in jsp page based on contents found in xml file? I have written jsp servlets and java class file. i transferred automatic data from jsp to servlet then to...

Javascript

How i can populate all fileds dynamically in jsp page based on contents found in xml

by: vijendra | last post by:

How i can populate all fileds dynamically in jsp page based on contents found in xml file?I have written jsp servlets and java class file. i transferred automatic data from jsp to servlet then to...

Java

Populate a Table based on Dlookup values in a Form

by: dmeyr | last post by:

Hello, I am new to Access and am having difficulty with a Dlookup function. I have a form that I wish to autopopulate 10 fields with values based on two criteria which are also fields on the form....

Auto Populate Based on fields from another table

by: joshua.nicholes | last post by:

I have an access database that consists of two tables.A data collection table and a species list table. The data collection table has about 1500 records in it and the species list has about 600....

Populate table with stored proc

by: Nate | last post by:

I am looking to populate a Schedule table with information from two other tables. I am able to populate it row by row, but I have created tables that should provide all necessary information for me...

Microsoft SQL Server

Auto populate a field based on selection from Combo box

by: whamo | last post by:

I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt...

how do you populate one table with data from another table?

by: toofunny24 | last post by:

I have two tables and both have identical fields for the most part. Both tables are linked by product id. One table is used for entering actual data. Table two contains the specs/target numbers...