Add Data Using Query

[jegesmaci]

Hello All,

I am new to this wonderful forum and I am amazed at the group of Access Professionals that is assembled!

I have a hopefully a short quick question.

I have setup a database that uses a form based on a query (because I need to calculate fields).

I would like to be able to add records to that query and have it add back to the table instead of just being update/read only.

Is there anyway to do this?

Thanks for the help!

Adam

 

[Tezdread]

hi Adam not sure what you're trying to do is possible, one of the more advanced db developers here may. I was wondering however why you would want to do this?

 

[jegesmaci]

How I currently have it setup is that I have about 4 fields that have formulas that calculate based on data input from the user.

It is not reccommended or easy to do calculations in a table so I created a query based off of the table and added the calculations.

My only problem is now I cannot add data to the form because it is based off of the query.

Anyone have any ideas?

Adam

 

[jegesmaci]

Anyboby! I would really appreciate a quick response.

Adam

 

[maxmangion]

it depends how your query is made up of in order to determine weather you can add new records (i.e. weather your recordset can be updatable). You can try do your calculations at form level by creating unbound controls and set your formulas there.

Otherwise, try to explain further how your query is built i.e. what fields do you have and from which table, maybe someone could suggest you how to make it updatable.

 

[jegesmaci]

So if I switch back to a table driven form and have the form do the calculating will that work, right?

And then when I need to work with the data I can use the query that includes the calculations, correct?

Thanks,

Adam

 

[maxmangion]

ideally you should base your form on a query, but i would do the calculations at form level, however, since i did not see how your query is done, i cannot guarantee that this will solve your problem.

 

[jegesmaci]

maxmangion,

Thanks for your willingness to help. Here is a copy of the database where I use the Query for the form, but I cannot add any records.

Thanks again!

Adam

 

[maxmangion]

which is the form that is giving you the trouble ?

 

[jegesmaci]

frm_contract_qry

Is it possible to change the relationship type and that would solve the problem altogether?

Adam

 

[maxmangion]

i took a quick look at your database, and the first thing you have to do is normalise your data. For example instead of having all those fields related to a doctor in both the tbl_contract and tbl_contract_export, you should create a tbl_doctors table and then simply use DoctorId in the other two tables for relationship. This way you will remove redundant data, and will be easier to maintain.

 

[jegesmaci]

maxmangion,

Thanks for the suggestion. Actually the tbl_contract_export is based on a make table query that pulls data from all tables and sends it out to excel. tbl_contract_export isn't a permanent table and only contains data based on a certain status that we export.

The Doctor information is only listed in one table.

Thanks again for the help. If you have any idea on the adding to a query, please let me know.

Adam