Using 1 form sourcing from imported and linked DB

[rplohocky]

I'm new to Access 2010. I created a imported DB and then created a form which works fine but after realizing that the DB does not allow formulas in them, the imported DB will not work.

I built the DB in excel and some of the columns have formulas in them, such as "if C2 = "sales" then return "done", or something like that. I have a couple columns that use the current date in the to execute the formulas, basically they are being updated automatically, by the date, everyday. The DB is fixed. I know I could link the database and it would return the correct data but then the only way to change the data is through the excel DB. I will have 25 people updating the DB everyday which is why I'm using Access but I can't have more than 1 person working in excel at a time.

Is there a way where I can design the form that will allow certain fields/columns to be updated by the end users and then have a subform be linked to the same DB on the fields/columns where the data is being updated by the formulas?

If this is possible or you have another idea how I can make this work please be specific on how to do this because I'm pretty new at Access, I know excel very well, writing macros and such but Access is still a learning curve.

Thanks for any help!

 

[Ranman256]

Formulas are done in the query.

 

[plog]

Excel to Access isn't as easy as people think its going to be. Access isn't Excel with forms and reports. It's a whole new mindset. You can't just simply import your spreadsheet into Access. You have to properly set up your tables through a process called normalization (https://en.wikipedia.org/wiki/Database_normalization). Read up on that, work through a few tutorials and then apply it to your data.

My suggestion is to move back to Excel until you can get your database properly configured. Work on setting up your tables correctly, then work on getting the data out of it like you want (through queries and Reports), then work on building input forms and finally move your live Excel data into your database and get off Excel that way.