Results 1 to 14 of 14
  1. #1
    Join Date
    May 2003
    Posts
    11

    Angry Unanswered: Make table Query

    please help me.

    When I import a XML document in to access the table look like as follows

    Item Description (Text Type)
    100 Lobby
    100 Length 250M
    100 Width 175M
    121 Main Door
    121 By Glass
    131 Paint
    ---- ------


    How can I make a another permanent table as follows using query

    Item Description(Memo Type)
    100 Lobby
    Length 250M
    Width 250M
    121 Main Door
    By Glass
    131 Paint

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: Make table Query

    Originally posted by SOF
    please help me.

    When I import a XML document in to access the table look like as follows
    Code:
    Item	Description (Text Type)
    100	lobby
    100	Length 250M
    100	Width 175M
    121	Main Door
    121	By Glass
    131	Paint
    ----	------
    How can I make a another permanent table as follows using query
    Code:
    Item	Description(Memo Type)
    100	Lobby
    	Length 250M
    	Width 250M
    121	Main Door
    	By Glass
    131	Paint
    i don't thing you can change the data type while doing a make table query but if you create the structure manualy you can then append the data to it without any problems
    the sql for that query is

    INSERT INTO <Destination Name>
    SELECT Item, description
    FROM <Source Name>;
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    May 2003
    Posts
    11
    Thanks a lot for your reply,


    Yes, I have designed the table manually, But My problem is what is condition i have to put to combine the description values when the Item is same.

    For Example if Item is 100 then Description Value for this Item 100 should be as follows
    Lobby
    Length 250M
    Width 250M

    in this table my point is there should not be duplicate in item.

    Hope you get the Problem, may be i have describe the problem last time in a wrong way.

    please...

    Appriciate your cooperation.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by SOF
    Thanks a lot for your reply,


    Yes, I have designed the table manually, But My problem is what is condition i have to put to combine the description values when the Item is same.

    For Example if Item is 100 then Description Value for this Item 100 should be as follows
    Lobby
    Length 250M
    Width 250M

    in this table my point is there should not be duplicate in item.

    Hope you get the Problem, may be i have describe the problem last time in a wrong way.

    please...

    Appriciate your cooperation.

    then your'l need multiple queries the first would be
    INSERT INTO <Destination Name>
    SELECT DISTINCT Item
    FROM <Source Name>;

    the second something like
    UPDATE [<Source Name>], [<Destination Name>] SET [<Destination Name>].[Description] = [<Destination Name>].[Description] & " " & [<Source Name>].[Description]
    WHERE [<Source Name>].[ID]=[<Destination Name>].[ID];

    and as for
    "Appriciate your cooperation."
    that sounds like a cheesy one liner from a gangster film ie the gangster says it to someone just before shooting them
    Last edited by m.timoney; 07-09-03 at 10:23.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    May 2003
    Posts
    11

    Talking

    Thanks again

    Still i did not get the result i want.

    if you don't mine let me detail you

    In my source table for each item value there are one or more description values, i want to group this according to Item. becouse this will combine the whole description in to one field value. some times this description value having more than 10 to 15 values in source table.

    so when i combine this for one item there will be only one description (Which combined one or more rows from source table)

    If you like to see the sample structure i can attach the database.

    And also is it posible to have the description in source line by line in one memo value.

    Thanks in advance.

    I will not shoot you like ganster, but ...........

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    when run mine (ie run 1st query then run 2nd query) gives
    Code:
    id	desc
    100	 lobby Length 250M Width 175M
    121	 Main Door By Glass
    131	 Paint
    if you change the '& " " &' to '& Chr(13) & Chr(10) &' you will then get a newline instead of a space

    if thats not what you mean then i'm sorry but i don't see what your getting at
    Last edited by m.timoney; 07-09-03 at 10:31.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    just noticed that i get a extra filling character at the start

    UPDATE [<Source Name>], [<Destination Name>] SET [<Destination Name>].[Description] = IIf([<Destination Name>].[Description] Is Null,[<Source Name>].[Description] ,[<Destination Name>].[Description] & Chr(13) & Chr(10) & [<Source Name>].[Description])
    WHERE [<Source Name>].[ID]=[<Destination Name>].[ID];

    this should fix it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    May 2003
    Posts
    11
    Thanks timony,

    It Works fine, I am really sorry last time i did a small mistake.

    Really grade idea.
    Nice to be member of this Forum,

    Thanks Timony again, well done.

    Thanks to Forum.

  9. #9
    Join Date
    May 2003
    Posts
    11
    Timony,,

    Sory for the disturbance,

    I have nother small problem When this two query runs,

    I have a small form with a command button, Where i click the Save Button First and second query run in order (By Docmd). So it will make the Second table as i needed, and it works fine.

    But in the first table if for same item additional description comes, in that case when i click Save it is updating same description again to all items (Duplicating Description inside the particular Description value). how can i prevent this and in case of additonal description only that part and only for the particular item description should updated.


    Thanks,

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by SOF
    Timony,,

    Sory for the disturbance,

    I have nother small problem When this two query runs,

    I have a small form with a command button, Where i click the Save Button First and second query run in order (By Docmd). So it will make the Second table as i needed, and it works fine.

    But in the first table if for same item additional description comes, in that case when i click Save it is updating same description again to all items (Duplicating Description inside the particular Description value). how can i prevent this and in case of additonal description only that part and only for the particular item description should updated.


    Thanks,
    i'm not quite sure what you mean can you post some examples?

    ps it's timoney not timony or if you prefer michael
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    May 2003
    Posts
    11
    hi m.timoney

    Thanks a lot again,

    I found a solution, it works for my my need code is as follow, But i don't know this is a professional way.

    Thnaks,


    DoCmd.RunSQL "INSERT INTO TempTable ( Item, Description )SELECT Table1.Item, Table1.Description FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.ID WHERE (((Table2.ID) Is Null));"
    DoCmd.RunSQL "INSERT INTO Table2 ( Id ) SELECT Table1.Item FROM Table1"
    DoCmd.RunSQL "UPDATE TempTable, Table2 SET Table2.Description = IIf(Table2.Description Is Null,TempTable.Description,Table2.Description & Chr(13) & Chr(10) & TempTable.Description)WHERE (((TempTable.Item)=Table2.ID));"
    DoCmd.RunSQL "DELETE TempTable.Item FROM TempTable;"

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by SOF
    hi m.timoney

    Thanks a lot again,

    I found a solution, it works for my my need code is as follow, But i don't know this is a professional way.

    Thnaks,


    DoCmd.RunSQL "INSERT INTO TempTable ( Item, Description )SELECT Table1.Item, Table1.Description FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.ID WHERE (((Table2.ID) Is Null));"
    DoCmd.RunSQL "INSERT INTO Table2 ( Id ) SELECT Table1.Item FROM Table1"
    DoCmd.RunSQL "UPDATE TempTable, Table2 SET Table2.Description = IIf(Table2.Description Is Null,TempTable.Description,Table2.Description & Chr(13) & Chr(10) & TempTable.Description)WHERE (((TempTable.Item)=Table2.ID));"
    DoCmd.RunSQL "DELETE TempTable.Item FROM TempTable;"

    if it works that is (mostly) all that matters

    glad it now working
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by SOF
    hi m.timoney

    Thanks a lot again,

    I found a solution, it works for my my need code is as follow, But i don't know this is a professional way.

    Thnaks,


    DoCmd.RunSQL "INSERT INTO TempTable ( Item, Description )SELECT Table1.Item, Table1.Description FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.ID WHERE (((Table2.ID) Is Null));"
    DoCmd.RunSQL "INSERT INTO Table2 ( Id ) SELECT Table1.Item FROM Table1"
    DoCmd.RunSQL "UPDATE TempTable, Table2 SET Table2.Description = IIf(Table2.Description Is Null,TempTable.Description,Table2.Description & Chr(13) & Chr(10) & TempTable.Description)WHERE (((TempTable.Item)=Table2.ID));"
    DoCmd.RunSQL "DELETE TempTable.Item FROM TempTable;"
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by SOF
    hi m.timoney

    Thanks a lot again,

    I found a solution, it works for my my need code is as follow, But i don't know this is a professional way.

    Thnaks,


    DoCmd.RunSQL "INSERT INTO TempTable ( Item, Description )SELECT Table1.Item, Table1.Description FROM Table1 LEFT JOIN Table2 ON Table1.Item = Table2.ID WHERE (((Table2.ID) Is Null));"
    DoCmd.RunSQL "INSERT INTO Table2 ( Id ) SELECT Table1.Item FROM Table1"
    DoCmd.RunSQL "UPDATE TempTable, Table2 SET Table2.Description = IIf(Table2.Description Is Null,TempTable.Description,Table2.Description & Chr(13) & Chr(10) & TempTable.Description)WHERE (((TempTable.Item)=Table2.ID));"
    DoCmd.RunSQL "DELETE TempTable.Item FROM TempTable;"
    the key factor is that it works you can always improve the method at a later date if you find a better method
    Last edited by m.timoney; 07-15-03 at 12:06.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •