How do you link a primary key to another input

[Zetheroth]

Ive got a table in which I store my buyers and a auto number for which buyer is which, how do I link another table to where when I put in the name of a buyer it pulls up the number from the primary key, and is this step even necessary for being able to create a report latter?Sales Examples.zip

[mike60smart]

If you explain your process we would be able to help that much easier.

The Buyer does what?

[Welshgasman]

You do not put the name of the buyer, in the other table, you put the ID of the buyer as a foreign key (FK), then use that to link back to the buyer table.

[Zetheroth]

in my sales table I put in the buyer name through a form, I just wanted to link the auto number to the name of the buyer I select, as this would create a more healthy relationship then keeping track of buyers by there name only.

[orange]

@zetheroth,

You are asking a very basic question concerning relational database.
I recommend you download and work through the sample tutorial below.
Working through will take about 45 minutes and you will experience what is involved in tables and relationships. Possibly the best use of 45 minutes. You will learn and what you learn can be used with any database.
XYZ Corp tutorial

[Welshgasman]

in my sales table I put in the buyer name through a form, I just wanted to link the auto number to the name of the buyer I select, as this would create a more healthy relationship then keeping track of buyers by there name only.

And when Acme Trading becomes Beta Trading, it all goes tits up?
Now you might want to treat Beta Trading as a completely new buyer, but that then removes all the history of when they were Acme Trading.
So providing you have no spelling errors with the name, you could link by the name. However ID would be so much easier.

TBH you are doing things backwards.

[Zetheroth]

a tutorial sounds perfect, thank you, will go through it tonight

[Zetheroth]

And when Acme Trading becomes Beta Trading, it all goes tits up?
Now you might want to treat Beta Trading as a completely new buyer, but that then removes all the history of when they were Acme Trading.
So providing you have no spelling errors with the name, you could link by the name. However ID would be so much easier.

TBH you are doing things backwards.

Then how should I do things? how would I go about having it store the information in the customer id, but input customer name when I add a sale? Is that even doable?

[Welshgasman]

I would use a combo, which displays the name, but stores the id. That is one of the normal ways to do it.

[orange]

a tutorial sounds perfect, thank you, will go through it tonight

There are many articles and links in the Database Planning and Design link in my signature. Also in that link are 3 other tutorials from RogersAccessLibrary - Class Info System, Catering Business and Widgets (just in case you want to try a few more tutorials).
Good luck.

[Zetheroth]

ok, will look into that to tonight

[mike60smart]

Study the Relationship Diagram attached.

This will allow you to create Form as shown in the Attached.

[Zetheroth]

jesus, man just did what I needed to do, now to figure out what he did.

[orange]

Work through the tutorial when you get a few minutes. (just sayin-- you'll learn)

[mike60smart]

Hi

Steps taken:

Sales table
1. Renamed Sales to "tblSales"
2. Got rid of spaces in the fieldnames
3. Changed the name of Buyer field to BuyerID and made it a Number , LongInteger Data Type.
You had this field as a Text Data Type and it was set as a Lookup
4. You can delete the field named Buyer as it is no longer required.
5. Did the same with BaleType changed to BaleTypeID - Number - LongInteger and NOT a Text Lookup field.
6. Deleted the calculated field which you were using to obtain the Total Sale Price.

Buyer Table
1. Renamed to tblBuyers
2. Removed spaces in names

Bale Types Table
1. Renamed to tblBaleTypes
2. Removed spaces in names

Created a Form named frmSales
1. Used the Wizard to create a Form based on tblSales
2. In Design View I deleted the Control named BuyerID
3. I then created a Combobox using the Wizard that allowed me to
Lookup the values from tblBuyers - in the last step of the Wizard
I stored the BuyerID
4. I followed the same process for BaleTypeID
5. I then used an Unbound Control to work out the Total Sale Price

All of the steps I have taken are covered in the Tutorial that Orange has mentioned.