Search multiple columns in table

[stone13]

Hello. Thanks for having this great forum.

I am in over my head in a database I decided to build. I am organizing an inventory management system and have this last bit to do:

I have three types of part numbers, each stored in a seperate column in the same table. A single part may be assigned all three types of part numbers. I need to track in the incomings and outgoings of these parts. So, I want to be able to open a form, type one of the three numbers, find the part and have information displayed on the form. Then I want to be able to type in the number of parts either going out or coming in, and have that automatically updated in the "On Hand Quantity" column in the Inventory Table.

And there is more: it would be great if how many of each part is coming in and out could be tracked in an other table.

Like I said, I am in over my head and need some help.

 

[M8KWR]

How does this sound.

You have a form that you can search on all three columns where the part numbers are kept, this then displays the results on the form.

Is the "On Hand Quantity" in the same table as the part numbers if so it would make this even easier to do.

If you could let me know the table name(s) and the field names then i can write the code for you etc. Let me know what field you would need displayed on the form, and which fields you need to search on.

Hope this helps.

 

[stone13]

Thanks for the offer. I split most of my data into seperate tables. Being a beginner, I thought it would give me more flexibility as my abilities improved.

So, I have a table called "Part Numbers" and another called "Inventory Levels". Within table "Part Numbers" I have columns as follows: "Part Number", "2d Part Number", "3d Part Number". Within table "Inventory Levels" I have columns as follows: "On Hand Quantity" , "Reorder Point", "Reorder Quantity".

I then built the following query that allows me to search all part numbers and display the data on a form:

SELECT [Part Numbers].[SSCC Part Number], [Part Numbers].[Part Number], [Part Numbers].[2d Part Number], [Part Numbers].[3d Part Number], [Inventory Levels].Location, [Inventory Levels].[On Hand Quantity], [Inventory Levels].[Reorder Point], [Inventory Levels].[Reorder Quantity]
FROM ((Description INNER JOIN [Inventory Levels] ON Description.ID=[Inventory Levels].ID) INNER JOIN [Part Numbers] ON (Description.ID=[Part Numbers].ID) AND ([Inventory Levels].ID=[Part Numbers].ID)) INNER JOIN [Vendor Information] ON (Description.ID=[Vendor Information].ID) AND ([Inventory Levels].ID=[Vendor Information].ID) AND ([Part Numbers].ID=[Vendor Information].ID)
WHERE ((([Part Numbers].[Part Number]) Like [])) OR ((([Part Numbers].[2d Part Number]) Like [])) OR ((([Part Numbers].[2d Part Number]) Like []));


Once that data is displayed on the form, I would like to have a command button for "Issued" and one for "Received". A text box would pop up, and if, for example, I hit "Issued", then typed "2" into the box, then "2" would be added to my "On Hand Quantity" field automatically.

I also need to track usage data. So, when that 2 is added, I would like the 2 to be stored in a seperate data along with the part number and date.

I know I am asking a lot, so anything you can offer would be great.