Help needed With Update Query Setup

[NewInAccessBusiness]

Hello all,

I have a small problem. I'm trying to create an update query for my DB, and i'm trying to set it up so that it only adds new MFR, without taking out the old ones.

The problem resides in the fact that the query always ends up deleting all of my MFR, replacing it with the one i wanted to add.

Does anybody know a way that the query can be set up so that it only adds the new data, withouth taking out the old one?

And if this is the wrong query for such a thing, which query type do you suggest?

I uploaded the file with the project in Access 2003 version, but i always use the 2010 version.

[orange]

I have looked at your database. Here are some recommendations that you should consider.

- Do not use spaces in your field and object names.
- Do not use special characters such as # in your field names.

Do some research on database design before you go too much further. There are some basics that are key to making good use of the software and reducing maintenance efforts. You will prevent/avoid a lot of frustration if you spend a little time with the following.

http://www.rogersaccesslibrary.com/forum/topic238.html

Read the first 3 topics (at least) in this link. You will gain valuable insight into data base design. Spend some time with Normalization (and why) and Entity relationship Diagramming.

Good luck.

[NewInAccessBusiness]

Indeed, it has helped me greatly with the maintenance area, and with entity relationship diagramming. It is my first year of learning to use access, so i wasn't taught that yet. I had a unproper relationship earlier, and i have now fixed it better so that all works well without needing too much checking.
The only problem that remains is that my initial problem with the update query remains unsolved. Do you might have the answer to it?

[June7]

An update SQL does just that, updates (modifies) values of existing records. That is what is happening with your db. If you want to add new records then must use INSERT (aka APPEND). Why not just open the table and type in new record or build an interface form?

[NewInAccessBusiness]

Altough the idea of a interface form is a good one, my "client" is in no need of them.
There is a problem with the db if i try to open the table and add in the new data. One of them is the fact that once i insert the new type inside the table, the scroll down has the ability to show me the new type, but the subdatasheet set in the table does not show the data. Any help in that sector?

[orange]

As has been pointed out already by June7, an UPDATE query will update the values of an existing record or records based on your criteria. It will NOT add a new record.

To ADD, or INSERT a new record, you would use an APPEND query whose syntax is along these lines.
"INSERT INTO...."

[NewInAccessBusiness]

As has been pointed out already by June7, an UPDATE query will update the values of an existing record or records based on your criteria. It will NOT add a new record.

To ADD, or INSERT a new record, you would use an APPEND query whose syntax is along these lines.
"INSERT INTO...."

Sorry about that, i should have been a bit more specific. I have rarely used the Append queries, and not very good at working with SQL coding. Do you maybe have an example of a code that i could use in an Append query?

[June7]

Use the Access Query Designer and Access Help has info on Append query. Refer to this tutorial site: http://www.w3schools.com/SQl/default.asp

[orange]

I have looked at your database again. In my view you need to normalize your data and design your tables. You have 6 tables all with the same layout.
Desktop
Printer
Copier
Others
Laptop
Machinery

Perhaps you could tell us exactly what is the purpose of your database. And then people could comment/advise/recommend some options.

[NewInAccessBusiness]

June7- Thank you for the SQL tutorial site, it is proving to be very helpful, although it is going to take me a while to be able to use SQL coding effectively, so i still need an example of what an effective Append query code might look like.

Orange- My client requires a database from which he can list many of the electrical hardware located inside his working facility, so i tried to set it so that the Machinery table contained all of the data regarding the equipment, i was trying to set the 4 extra tables - Desktop, Laptop, Copier, and Printer - to contain the type of hardware that his facility has the most of (which are the names of the tables). For any outliers, i set up the Others table so that any info that does not fit into the 4 tables could be entered here. All of the data entered inside the 5 tables should also be located inside the Machinery table as well.
I was trying to set up the MFR and the Type fields in each table to be connected to the tables that should contain their specific types. For example, the MFR List table should contain the manufacturers that created that piece of hardware, and the MFR field would be set as a lookup scroll down connected to the MFR List, so that any changes made to the MFR List table should affect the scroll down of all MFR fields. The same goes for the Type fields and the Type List table. It just so happened that the relationship i had created formed a subdatasheet inside the MFR List and the Type List tables, so i want to see if i can keep the subdatasheets updated as soon as somebody enters that specific MFR or Type inside the fields.
I was trying to set up an append query so that, once activated, would show a parameter-based question on adding any new manufacturers to the MFR List table. Once that query was set up, i would have created a copy of the query for the Type List table.
These are the most troublesome parts of trying to make my database, so i'm asking help in those. The simple queries that i shall have to create i'm leaving out of the description, simply because all that is required of them is to find the specific hardware and various data about the hardware, so all i need there are a few simple queries.
I thank those that have helped me so far, and i thank in advance those that might.

[orange]

NewInAccessBusiness,

I feel you have a basic design issue. You should normalize your tables. See the reference I made in post #2. It will help you with data structure. In addition I recommend you make a list of the rules surrounding your database and the business it supports/will support.

Each of the data models at http://www.databaseanswers.org has associated Business Rules. These Business Rules will be the guidelines for your database design and testing.

You may also find http://www.databaseanswers.org/approach2db_design.htm helpful. It is generic, but will give you a basic approach.

The data model - http://www.databaseanswers.org/data_...ance/index.htm shows how to relate Equipment and EquipmentType, but it has other detail which seems to be outside the scope of your project.

[June7]

I agree with orange. This is not a valid relational database structure. The supplementary tables for the different types of equipment are not required. They repeat data in the Machinery table. Instead of parameter prompts and direct interaction with tables, should build forms as GUI. And reports to output data. These forms and reports would have as RecordSource the tables or queries that are joins of related tables. Queries are the vehicle to manipulate raw data in tables - sort, filter, calculate.

[ssanfu]

And if normalization hasn't overwhelmed you, here is a little more. Along the lines of "don't use spaces in object names", don't use reserved word for objects either. Here is a list of reserved words in Access and SQL:

http://allenbrowne.com/AppIssueBadWord.html


And knowing the "The Ten Commandments of Access" wouldn't hurt......

http://access.mvps.org/access/tencommandments.htm


Enjoy....

[NewInAccessBusiness]

Thank you all for the great sites

orange-you're right, according to your post #2, my data needs to be more normalized than it is now, thank you and everyone else for pointing that out.

June7- unfortunately, i do not recognize what you mean by me needing to build forms as GUI, could you show an example or description somewhere plz?

ssanfu- the list i almost passed, took me a long time to go through it all, but thank you for it. The 10 commandments were also quite 'insightful', altough i passed all but the lookup list one.

To all- I will keep on working on the project, and update the new DB online as soon as i made the most important changes. Plz keep on posting more advices to keep making my DB better and better. Thank you all for your help.

[June7]

GUI: Graphical User Interface
This means forms. Forms for menus, data entry, search criteria. Access Help has info on designing forms.

I never use parameter prompts in queries. Too hard (impossible?) to validate user input.