i need help with simple query 1

[TheunsGoosen]

Access beginner!
Good Day, can someone help!

In the 1st table I have the following two fields:
BussinessPartner and Bill-to City

In the 2de table the following to fields
BusinessPartner and City

If you select BusinessPartner in the second table a 2 column dropdown combo box appears displaying the two fields of the first table, on selection I would like the query to take only the second column, Bill-to City, to display it in the City field of the second table.

I don’t get the code right?

Thanks
Theuns

 

[lespaul]

What's the difference between Bill-to-City? When you say

"display it in the City field of the second table"

do you mean you want to update the table?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[TheunsGoosen]

In the second table the BussinessPartner column count is set to two, so if you open up the second table it is displaying BusinessPartner and Bill-to City. On selection i want the second column count value to display somwhere else.

something like [businessPartner].column(2)
but this is not right. Query is not working.

Theuns

 

[lespaul]

you can't display in the table, only in a form. I still don't understand what you are trying to accomplish?

In the second table the BussinessPartner column count is set to two

This doesn't make any sense either. ColumnCount is usually found in a combobox where a query source has a PK and a name and you want to update a table with the PK (as a FK) and display the name for users to select from.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[TheunsGoosen]

Thanks for the help.
My lack of Access understanding and terminology makes it hard to explain.
I’m not familiar with all the rules of a forum, but if I could send the file to you it will be great, if not, thanks for the responding.

Theuns

 

[lespaul]

If you select BusinessPartner in the second table a 2 column dropdown combo box

Do you mean you have a lookup field in the TABLE?

I would suggest you do a little reading up on databases. First, the fundamentals document linked in my signature is one of the standards. Additionally, you should read The Evils of Lookup Fields in Tables.

Access should be set up so that there are tables and queries that are shown with Forms. Users shouldn't have direct access to the tables at all (in most situations).

What does your database do?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[TheunsGoosen]

Hi Leslie,
I have a component table with all component information;
ID Description
01 Screw 16x20mm
01 Screw 25x30mm

I also have a Vendor information table;
ID BusinessPartnet Bill-to City
01 KB engineering New york
02 MD engineering North Carolina

also Component_Vendor_Sub ID table
ID ID Business partner Prise
01 01 MD engineering R2.8
02 01 KB engineering R3.1
(thus this table is displaying that one can buy the 01 ID componet at two diff suppliers at the prices above)

In the Component "Form" one can move through the component record selectors and its working perfectly. But in the Component "Form" i have a subform displaying the component Buy-in Vendor information so that one can add Business suppliers, like so;

ID ID Business partner Bill-to City Prise
01 01 MD engineering New York R2.8
02 01 KB engineering North Carolina R3.1
03 01 jj engineering South Carolina R3.0

The drop down list to a sub record works perfectly when one is adding a extra suppliers from the drop down box, But in the subform the Bill-to City should then just jump in place according to the Vendor information table. But this is not the case...it is also a dropdown list??

Regards
Theuns

 

[lespaul]

Ok so you have a form with a sub-form. In the sub-form you want to display this information:

[tt]ID ID Business partner Bill-to City Prise
01 01 MD engineering New York R2.8
02 01 KB engineering North Carolina R3.1
03 01 jj engineering South Carolina R3.0[/tt]

and you want that information updateable?

What is the source of the subform?

Additionally, in looking at your tables:

[tt]
tblComponents
ID Description
01 Screw 16x20mm
02 Screw 25x30mm (I think your ID was wrong before)

tblVendor
ID BusinessPartnet Bill-to City
01 KB engineering New york
02 MD engineering North Carolina

tblComponent_Vendor_Sub
ComponentID VendorID Prise
01 01 R2.8
02 01 R3.1
[/tt]

your final table tblComponent_Vendor_Sub should NOT contain the Business Partner field AGAIN. You get that information from the tblVendor. It should be structured like I show above and to get the Business Partner name you create a JOIN between the ForeignKey VendorID in tblComponent_Vendor_Sub and the PrimaryKey of tblVendor:

SELECT C.ComponentID, C.VendorID, V.BusinessPartner
FROM tblComponent_Vendor_Sub C
INNER JOIN tblVendor V ON C.VendorID = V.ID

storing the same information in two locations (like BusinessPartner in both tblVendor and tblComponent_Vendor_Sub) breaks the rules of normalization. Did you read the fundamentals document I suggested? It will really help you understand how the data should be stored in the tables to make extraction, reporting and developing much easier in the long run.

HTH





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[TheunsGoosen]

Leslie,

Thanks for the articals and the advice, i will give a go and let you know.
Theuns

 

[TheunsGoosen]

Thanks Leslie,

Your information helped me brainstorm the topic and everything is working perfectly.

Regards
Theuns