I am feeling my way through a task I am trying to undertake here at my job. We are using a pretty archaic system that doesn’t adapt well to the changing environment. I am trying to put some utilities in place to help the department have access to more data but with each problem I solve, I create a seemingly harder problem. I am one of a few people that have beyond the norm computer knowledge so I have been defaulted this task.
What I have is an access table that is imported from our production database. I import those daily. Then I wrote 2 queries that pull from our bill of materials and then a 3rd query that joins that to the schedule. I tried to do it in one big query but with the outer joins I had to use to get 2 part numbers I had to do them separately. Everyone has Excel but there are only 2 computers that have Access so I want to write the front end of this in Excel. I need to be able to change the categories in queries 1 and 2 from Excel. The caveat here is that I have to use that 3rd query to join the parts to the schedule because not all models take a part and we need to use it for error checking. The 3rd query, if a part isn't present leaves a space blank in the part number column. With out that join, the model is omitted and the schedule is out of sequence.
I would like to put in a user form in excel that I can pick what category I want the 2 parts to be. Part one would modify query one and part two would modify query two and then the data returned is query 3.
I'm sure this is possible, but I am worried about the complexity. I handle VBA with a moderate amount of efficiency but I am sketchy with SQL, mostly graphically organize and copy past the SQL where I need it.
Any help is much appreciated. Hopefully this all makes sense.
Bookmarks