How to Do Part Numbers - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

How to Do Part Numbers

David Clement

15 months ago

Dear Richard,
I am hoping there is a better way to do this. I am currently building a Part Sales Invoicing database mostly based on your Invoicing Template. It works, but still not quite what I am looking for. I am having trouble with the whole PartNumber, Description and BinLocation showing up as one record when searching for the PartNumber. This is where I get confused, you enter the Part Number you want. That Part Number has One Description and One Bin Location. My PartT has those three fields. Each Part Number has an PartID which is an AutoNumber.
How can I search for the Part Number and get the results showing Description and Bin Location as one record?
I have also created a Query using Concatenation for those three fields, but it looks kind of wonky.
Can you please help?
Thank you.

	David Clement OP @Reply 15 months ago

	David Clement OP @Reply 15 months ago
This is what I am hoping for: 101A/177.136B622= Part Number Brass PLC Union= Description 1000= Bin Location or something like that. Thank you.

	Richard Rost @Reply 15 months ago
Well, if you're only searching based on the part number, then just have that be the search. You don't need to concatenate the other fields if you're not searching by them, and then just join the results with the other fields.

	David Clement OP @Reply 15 months ago
This is how the original recordset is, keeping in mind that I didn't create this.They have it is as, Description=Part Number Notes=Description Bin=Bin Location Can you remind how to do join the result? Have fun in Washington at the Access Event.

	David Clement OP @Reply 15 months ago

	Richard Rost @Reply 15 months ago
Are those fields all in one table? If so you don't need a join. Just search on the field you want by itself and display the other ones.

	Julie Bennett @Reply 15 months ago
It seems to me that watching Richard's Search 2.0 video may be helpful. Have you tried a dedicated search form?

	David Clement OP @Reply 15 months ago
Thank you Julie, yes I have tried that and it works great. What I am trying to do is if I where to make an invoice, I would like the Part Number, Description and Bin Location to show up only entering the part number in a search so I can invoice it, just like the SQL database my company is already using. Thank you to all who have given me input on how to do this.

Richard Rost @Reply

15 months ago

If those fields are already together in the same table, then just make a query showing those three fields and perform the search on the part number. Then the other two fields should show up in your query. If not, if those are values from other tables, then join those tables together in the query. It's just like how I do the Customer Order query and the Tech Help free template. I joined together the Customer table with the Order table with the Order Detail table, and then you can display all the records you want from any of those three tables.

	David Clement OP @Reply 15 months ago
Thank you!

	David Clement OP @Reply 15 months ago
This is what I am ultimately trying to do, just so I know how to do it. Thank you for your help.

	David Clement OP @Reply 15 months ago

	David Clement OP @Reply 15 months ago
Richard, This is the form we use to generate an invoice. It is very much like your Invoicing Template, but with this one, you enter the part number, and you get all relevant information for that part. Still not exactly sure how to do the joining you mentioned. Thank you so much for your tutorials.

	David Clement OP @Reply 15 months ago

	Richard Rost @Reply 15 months ago
Is all of the information in the same table?

	David Clement OP @Reply 15 months ago
I can't access that, so I don't really know. I can't thank you enough for your tutorials. They have helped me a lot. But I think I will move on to other databases. This was only ever going to be a "hobby" project anyway.

	David Clement OP @Reply 15 months ago
My database has a CustomerT Table and a PartT Table.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View