how truncate one table so i can make a relationship with another

MS Office Access


  • 1. Count/Quantity field
    hi, i hav a table with these fields: range colour size the records will be of different combinations, and there will be duplicates, so what i want to do is create a query that displays an extra quantity field next to each different record, with no duplicates. I have only got so far as the "group by" and "count" function but i am now clueless as to how to apply it to the scenario. also is there any information about including quantities in databases? is it good practice to do that or should each individual product have its own record? TIA
  • 2. "Undefined function 'Date' in Expression"
    Hi, I am working on a network, on one computer I can use the Date() function in access. on the other computers I get: "Undefined function 'Date' in Expression" What can I do to ensure that this command is recognized? Thanks in advance, Eric
  • 3. Displaying an Error dialog box when #Error results
    I would like to display an Error dialog box if a function inside a query has a #Error as a result. I would like to have information from that row printed in the dialog box. Can this be done?
  • 4. Optimize Select insert and update
    Hi folks, Here is what I do: I perform a SELECT to see if a record is in the DB before performing an INSERT or UPDATE. I am receiving massive information to place in the DB. Is there a better way to do it? I use ADO recordset and ADO connector. Access 2000, win 2000. Thanks :)
  • 5. Query from Form when "Not Like"
    I have a form named MainForm in which I have a couple of unbound fields. I have a query named MainQuery in which I have criteria referring to those unbound fields in MainForm For example, one of the unbound fields in Mainform is called Location. In my database, I have a field called NA. In my MainQuery, I want that NA field to refer to that unbound Location field in MainForm. Following some great examples in this newsgroup, I've entered this for the criteria for the NA field in MainQuery [Forms]![MainForm]![Location The NA field can have any one of 5 letters in it: "e", "x", "v", "w" or "z When I enter any of these 5 letters in the Location field in MainForm and run MainQuery, everything comes out perfectly However, I often need to run the query excluding "v". If I enter "<>v" in the Location field in MainForm and run MainQuery, it doesn't work. Short of going into MainQuery and manually changing the criteria to Not Like "v" each time I need to do this and then changing it back when I'm done, is there any way I can accomplish this either by some different syntax in criteria, or altering something in the unbound Location field in MainForm or perhaps some "if" statement in MainQuery Many thanks Paul Simon

how truncate one table so i can make a relationship with another

Postby QU5EWSBDQUxMQUdIQU4 » Mon, 16 Oct 2006 19:08:02 GMT

I have several tables that are linked with a common field, but one particular 
table needs formatting to make a relationship

table order number shows as AD123.1 (table1)the other table AD1230000( table 
2).  I am looking for an expression to truncate both tables to have a field 
in each showing orders as AD123 to enable the linkage

thanks Andy  

Re: how truncate one table so i can make a relationship with another

Postby David F Cox » Mon, 16 Oct 2006 19:56:05 GMT

It looks like LEFT([yourkey],5) should do it.

You could create two queries to pre-select and massage the data with :
shortkey: LEFT([yourkey],5)

and use these as inputs to the queries that you wish to run.

Re: how truncate one table so i can make a relationship with another

Postby John Vinson » Tue, 17 Oct 2006 04:36:40 GMT

On Sun, 15 Oct 2006 03:08:02 -0700, ANDY CALLAGHAN

I'd VERY strongly suggest splitting this field into two in table
design view. Fields should be atomic; it appears that the Order number
consists of two parts, each with its own life - the AD123 and the rest
(1 or 0000 depending on the table).

Note that a Primary Key, and a table join, can consist of two (or ten
for that matter) fields, and that you can concatenate them for display

                  John W. Vinson[MVP]    

Similar Threads:

1.Making a one to one relationship

Access 2003 automatically selects the relationships now.  in Access
2000 it used to be a dropdown box to select a one to one relationship
when right click the relationship line.  How do I now change a one to
many relationship between a Primary key and a Foreign key.
cheers in advance.

2.Making many rows in one table out of one row from another table

I have one table that has a quantity value in it.  I need this quantity to 
have its own row in another table that is autonumbered.  If the quantity was 
5 I would need 5 rows with a few columns of data pulled into the other 
table.  How can I make access autogenerate x amount of rows in another 

3.Updating the one table in one to many relationship

I have two tables: Orders and payments.  Each order can 
have zero to many payments.  The order table has the 
order number, name & address, amount owed and date 
shipped.  The payment table has the order number, a 
payment reference number (check number for example) and 
an amount paid.  

An order is filled if the date shipped is filled in AND 
the total of the payment amount equals the amount of the 
order in the Order table.  I want my "Open Orders" form 
to display only open orders (ship date blank or sum of 
amount paid does not equal amount owed).

How can I do this so I see only open orders and I can add 
the date shipped, for example, to make it drop off the 
form?  Every way I try I get an unupdatable query.

4.Creating a Form bounded to three tables with one-to-one relationship

5.Help with SQL insert into 2 one to one relationship tables

Hello all

i have 2 tables that are in a one to one relationship. the main table is the 
product table with a ProductID and a ProductTypeID. these 2 fields are the 
primary key on the tblProductList. i have another set of tables that stores 
the configuration of each type of product. the other table that i am trying 
to update through code at the same time as the tblProductList is the 
subdatatblCompressor. the ProductID in the tblProductList is an autonumber 
field. the ProductTypeID is a text fields that can be a few different values, 
"Compressor" is one such value. when a compressor is added to the database it 
puts one record in the tblProductList and the related configuration is stored 
in the subdatatblCompressor. the autonumber value can only be used once in 
relationship to the related record in the subdatatblCompressor.

i hope all that makes sense.

when we add new products we use a form that allows us to do a lot of other 
things other than just adding a new product, it also allows us to import 
related information to other tables and fields. this new product "building" 
form is unbound and therefore it uses code to manage the additions and 
"linking" of the other information. 

this code was working fine. i have been working on it for over 3 hours 
trying to figure out why it has stopped working. at some point in the last 
couple of weeks i made a change to allow for some additional new features to 
the form and in doing so i broke something.

i keep getting an error message of duplicate key values being created. i 
checked out the line that the debugger points to and have played with it as 
much as i can.

here is the relevant area of code:

        'gather variables and insert into the product table.
        If (error = False) Then
            If Me.cboProductType = "Compressor" Then
                sql = "INSERT INTO tblProductList (ProductTypeID, 
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '" 
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
                MsgBox sql
                DoCmd.RunSQL sql
                sql = "INSERT INTO tblProductList (ProductTypeID, 
SerialNumber, CustomerOrder, SiteID) VALUES ('" & Me.cboProductType & "', '" 
& Me.SerialNumber & "', '" & Me.CustomerOrder & "', " & site_id & ")"
                MsgBox sql
                DoCmd.RunSQL sql
            End If

            ' find the last productid that was created and store it into the 
variable product_id
            Set cn = CurrentProject.Connection
            Set rst_newproduct = cn.Execute("select MAX(ProductID) from 
            product_id = CInt(rst_newproduct(0)) ' convert the number into 
an integer
            MsgBox "ProductID = " & product_id
            Set rst_newproduct = Nothing
            'if the user entered information into the CBA #, Electrical 
Panel WO, and the PLC Panel WO fields insert them into the compressor 
information table
            newCompressorCheck = Me.CBANumber & Me.ElecPanelWO & Me.PLCPanelWO
            MsgBox newCompressorCheck, vbOKOnly, newCompressorCheck
            If (Not IsNull(newCompressorCheck)) Then
                'find out the subdatatblCompressor highest productid in 
subdatatblCompressor just to know if it will be duplicated
                Set cn = CurrentProject.Connection
                Set rst_newproduct = cn.Execute("select MAX(ProductID) from 
                subdataproduct_id = CInt(rst_newproduct(0)) ' convert the 
number into an integer
                MsgBox "ProductID = " & subdataproduct_id
                Set rst_newproduct = Nothing
                sql = "INSERT INTO subdatatblCompressor (ProductID, 
CBBAEngMaster, ElectricalPanelWO, SkidPanelWO) VALUES ('" & product_id & "', 
'" & Me.CBANumber & "', '" & Me.ElecPanelWO & "', '" & Me.PLCPanelWO & "')"
                MsgBox sql
                DoCmd.RunSQL sql
            End If

is there anything immeadiately obvious that is wrong with this?

i thought maybe the quotes around my SQL statements might be wrong, but 
after playing with them for a while i havent been able to make a change in 
the error i am getting.

if there is any other information i should post let me know. thanks in 
advance for any help with this.

6. Data in table or one to one relationship

7. Merging related records when there are multiple one-to-one relationships between two tables

8. displaying a one-to-many relationship in one table

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 68 guest