473,322 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Relationship between two tables.

I am trying to set up a subform that has two tables.

Table 1: ID DESCRIPTION UNIT PRICE (ID=Primary Key)

This table is fix price, information will never change. "Description" is a look up box.

Table 2: IDqty quantity ID (IDqty=PK)



The relationship is one to many. Table 1 "ID" being one to Table 2 "ID" being many.


The problem I am having is that when I try out my query or form it is pulling the information I select back into Table 1. Like in my form I choose a "description" from the lookup box, the price and unit information should appear, but it doesnt. What happens is it is adding the same information back into table 1. Right now I have 82 records in table 1. It will add record 83 and store the same "description" I selected back into the table. NO Records should be added back into Table 1, it is a fix rate table.

What I want to do is when I create my form, I want to select "description" pick which record I want from the combo box. The unit and price should appear for each description I pick. Then I want to be able to enter a quantity.

I have tried changing the relationships, queries and forms.

Any suggestions?
Jan 17 '08 #1
5 2177
FishVal
2,653 Expert 2GB
Hi, there.

You should link the form's combobox to Table2.ID (I guess that is FK).
Jan 17 '08 #2
What do you mean? The combo box is set up as a lookup box from the table. The row source is coming from qryequipmentprices (query of table 1). How do I link table 2 to it?
Jan 17 '08 #3
FishVal
2,653 Expert 2GB
What do you mean? The combo box is set up as a lookup box from the table. The row source is coming from qryequipmentprices (query of table 1). How do I link table 2 to it?
First of all I would like to ask you to provide more information.
  • Post your tables metadata.
    Here is an example of how to post table MetaData :
    Table Name=tblStudent
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. LastAttendance; Date/Time
  • Post your query SQL.

Second - it should be obvious that entering data to a form control bound to a field in a table will cause this data to save in that field of that table.
Though you havn't specified what is the controlsource of the combobox you are mentioning, I've made a guess that the controlsource is a field in [Table1] as soon as symptoms are quite clear.
If you want a more helpful advice, then you should take more care on formulating your question.

Kind regards,
Fish
Jan 17 '08 #4
Table 1 Name= tblequipmentprice

1. Description; Text
(Combo Box with rowsource "qryequipprice"
2. ID; autonumber - PK
3. Unit; text
4. Price; currency

Table 2 Name= tblinstallqty

1. Idqty; autonumber - pk
2. quantity; number
3. ID; number

one to many relationship; ID from table 1 is one to ID table 2 is many.

query SQL

SELECT qryequipprice.DESCRIPTION, qryequipprice.UNIT, qryequipprice.PRICE, qryequipprice.ID, tblinstallqty.IDqty, tblinstallqty.quantity
FROM qryequipprice INNER JOIN tblinstallqty ON qryequipprice.ID=tblinstallqty.ID;


My goal is to have a subform that will let you select a description name, the prices and units will appear and I can enter a quantity and get a total.
Jan 17 '08 #5
FishVal
2,653 Expert 2GB
Ok.

As I've already said, you need to retrieve field tblinstallquantity.ID in your query and link it to form control.
Just to make a sense of how it is working run the query and enter some value in tblinstallquantity.ID field.
And out of curiosity why don't you want to use form/subform interface?

Regards,
Fish
Jan 17 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Alcyone Oliveira | last post by:
------=_NextPart_000_000D_01C36572.F6480CE0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_000E_01C36572.F6480CE0" ------=_NextPart_001_000E_01C36572.F6480CE0 Content-Type:...
4
by: H Cohen | last post by:
Hi, I have a corporate database with about 60 different tables that spans manufacturing, accounting, marketing, etc. It is possible, but unwieldy, to establish a relationship for each table...
2
by: Steve | last post by:
How do I delete a relationship between tables in SQL Server 7.0? I previously had a relationship between two tables, but I renamed the field and table of one of the tables and the old...
8
by: Andante.in.Blue | last post by:
Hello, I have just inherited a legacy Access 97 database. While going through it, I noticed something strange... its Relationships window (the one accessed by Tools --> Relationships) is almost...
2
by: Marco Simone | last post by:
Hi, I have 4 tables, tblCompany, tblA, tblB and tblC. tblA, tblB and tblC contain same type of data, they should be in one table, but since there are many fields, I split it into 3 tables. Each...
5
by: BrianDP | last post by:
This is a strange one-- I have an Access 2K database with a split front end/back end. There are two tables in the back end - RFile and RLine. There is a one to many relationship from...
10
by: Richard | last post by:
Hi folks, thanks for taking the time to read this (and hopefully point our where I'm going wrong). The scenario: I have a local Access2007 database which links in several read only mySql...
11
by: RobertJohn | last post by:
Hi all I am using Access 2007 to start a small home library application, and so far it has two tables. 1. Books, with fields Book_ID (Primary Key) and Title, and 2. Authors, with fields...
4
by: Phil Stanton | last post by:
Having spent ages sorting out the layout of my relationship window with about 60 tables and getting it all tidy and with enough of the tables visible to show the PK an FK relaionshipsOn a subseqent...
6
by: NicoleCartrette | last post by:
Going back to school is easier said than done.. This was posted to an older thread earlier but I don't think it got any attention. Your help is appreciated Professor requires we create a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.