Changing a select query to a append query for the newbie?

[rebfein]

Its me again,



I created two tables in my database, Component Status (active parts) and Component Status INA/OBS (inactive or obsolete parts). I created a select query and saved that as the second table for the first time.

I got hit with a list of several parts that our vendors are saying are now obsolete. Can I change the select query to an append query, where the query will select all the parts marked OBS and append them to the Component Status INA/OBS table?

And if so, how would I go about doing that.

Thank you,

Rebecca

[JoeM]

Are these two tables both structured the same (basically, with all the same fields)?
If so, this is not the recommended design (to have two tables). A well-designed database seldom has need for routinely moving records from one table to another.
What is recommended is to have one table, and simply have a field to indicate whether or no that part is active or obsolete.
Then all you need to do is update this field (and not move records between tables).
You can easily use this field as criteria in queries if you just want to return Active or Obsolete parts.

[rebfein]

Hi JoeM,

The purpose is to move a single (or multiple) deleted parts from two tables of active parts and enter them in a third (only one time) table for obsolete parts.

When a vendor lets us know that the parts we buy from them are going to become inactive or obsolete, I'd like to create a query that will delete the part number from our Part List (of all the parts we use in all of our products [Fields are P/N and description]) and our Part Comparison table (which shows multiple vendor parts for the single part we have use [Fields = our P/N, Ven#1 Name, Ven#1 P/N, upto 7 vendors and 7 p/n]).

Then, I'd like the query to append the records into our OBS table (manager required).

Is this possible or should this be a manual operation?

Thanks Rebecca

BTW, how do I get my picture to appear in my profile section? It's already uploaded, I just can't get it to appear.

[JoeM]

I am guess I am failing to see why there needs to be two separate tables for this.
If you have a flag to indicate whether a part is Obsolete or not, you just update this flag as a part becomes obsolete.
You can then just use this flag in your queries if you just want to return Active (or Obsolete) parts.
Make sense, or I am missing something?

BTW, how do I get my picture to appear in my profile section? It's already uploaded, I just can't get it to appear.

I am not an Administrator on this forum, so I really can't say. Looks like you can post that question here: https://www.accessforums.net/forum-suggestions/

[rebfein]

Fixed the pic problem, but thanks for the reply.I originally had a single table set up that way. Unfortunately, what you're not seeing is that this company changes most things VERY SLOOOOOOWWWWWWLLLLLLYYYYYYY. Our admins are still doing quite a few tasks manually (i.e., checking warranties and even printing addresses via typewriters), that would be faster and easier via computer.And the boss wants the obsolete parts in a separate table (so newbie engineers don't get confused and use them??). This is what I was hoping would be a good go-around.

[JoeM]

Quite frankly, moving things from table to table can be very dangerous, and can endanger your data integrity.
What you actually need to do that is two Action Queries, an Append Query (to add the records to the one table) and a Delete Query (to delete the records out of the other table). What happens if for some reason, one of these queries fails? Then you either have the records you are trying to move in both tables, or in neither table.

The recommendation I am making should actually save time, and help ensure data integrity (something most companies and database administrators appreciate)! Actually, many database administrators don't want anything to do with databases that are not normalized (I have actually had DBAs tell me that such designs are "red flags" to them that the database was designed by an amateur who probably doesn't know what they are doing, nice huh?).

As far as having them in separate objects is concerned, another rule of thumb is that users should NEVER have direct access to underlying tables. Everything should be Form driven, so you direct users to exactly where you want them to go. Forms can be based on Queries as well as Tables. So if you have a Query that returns only Active records, and a Query that returns only Obsolete records, and use these objects for the Forms in which users access the data, it will do exactly what you (and they) want.

That being said, if you really must keep the two table design, here is a good reference on how to create an Append Query in Access: https://support.office.com/client/Cr...8-a2638ccf4ad0. It actually starts off with telling you to create the Select Query, and then changing it to an Append Query (so you should be halfway there already).

[rebfein]

JoeM,

Thank you for validating that I'm not a complete idiot and amateur!

In the above conversation(s), you stated some items that I've brought up with my boss and the others who are suppose to be using this database (personally,I doubt most of them will).

Attached is a home page I created for all the items I thought they would need. As you can see, I created a two-tiered Navbar. I wanted to add the queries I created to it, but am not sure that they would run in this setting.

What do you think?

[JoeM]

Thank you for validating that I'm not a complete idiot and amateur!

Yes, I have come across some DBAs who are quite arrogant! Seems to come with the territory, in a lot of these IT fields.
I classify myself as more of an "Advanced Hack"! I know some of my methods make their eyes roll!

Regarding design, I typically have different menus. I see that you already have a tab for Reports. You could add a new one for Queries, if you look, and add command buttons to open various queries you have set-up there (or Forms based on various queries)...

[rebfein]

That is what I wanted, but I'd like to know, how would the queries 'run' if you can't click on the 'Run!' button?

[JoeM]

Regardless of whether a query is a Select Query or an Action Query, all you need to do to run them is Open them.

If you have a Form or Report that uses a Select Query as its Control (Data) Source, opening the Form or Report automatically runs that Query.
Also, if you have a series of nested queries (one query calls another query, etc), all you have to do is open the last one in line and all the other queries it uses are automatically run. So query results are always "real-time" when opened.

So all you need to do is have your command button open the Query, Form, or Report that you want.

[rebfein]

Any idea why when I drag a query to the nav buttons of the sub-nav bar a sub form is created? I thought I could drag and drop the queries as they were?

[JoeM]

I never heard of dragging and dropping queries. I imagine that if you try to drag and drop a query to an existing form, it would probably put it in a subform on that main form.

If you are just trying to create a link to the query, use a Command button and have it open the query.