Access DB Clarification

infiniti7181
infiniti7181 used Ask the Experts™
on
Dear Team ,

Kindly assist me for the below requirement  I have template in excel where i like to make it in excel as these value entries will exceed around 300 -400 lines . Based on this i will filter accordingly the categories for reporting .

Can you please help me out and advise, how can  i create the excel field as attached in access

Highly appreciate your support

Regards,
Sid
tmp.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Do you mean you want to store the data in Access? If so, then we'd need to know more about the data, what it represents, and what sort of business process you're trying to work with. Descriptions of the columns would help quite a bit - for example, what is "12MPS", "36MPS", etc.

Author

Commented:
Hi
thanks for the reply.

I would like to have the same date in Access .
Data is as follows :
ID#: Alphanumeric code
Client: Text
Description : Text
Revenue : Value in USD
Cloing Date : Date
FW : Pull Down or Select Menu ( based on the pull down list from excel )
Dept : Same as above
12MPS, 36MPS : These all fields will be filled with US Dollars
Finally last two columns will be Text

Attached file was a template, where i added two rows . I would like to automate in MS Access , so that i can  prepare report by filtering the rows . Lets say eg: Closing Date nearing to July 2018 . etc

Regards
Sid
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'm still not sure what you mean.

Are you going to create a Report in Access?

Is the data stored in Excel, or in Access, or will the user enter this data?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi ,  

Sorry for the confusion. What i meant was :

1. The user will enter the data in access , so that these entries can be extracted to excel sheet as shown in template excel file .

Author

Commented:
Of course data will be  stored in access, but later if needed , i can extract the information in excel .
Most Valuable Expert 2012
Top Expert 2014

Commented:
Okay - that makes sense.

In Access, in order to properly store data, you should carefully consider your database design. In your case, it seems as if you're storing information about projects, or portions of projects. Is that correct?

Author

Commented:
Yes . Basically i am storing information about projects . ( Eg: Values , Dates , Reference numbers etc .)

Author

Commented:
hi team  ,
is it possible ? any ideas or example to help me

Regards
Aaron
Most Valuable Expert 2012
Top Expert 2014

Commented:
Please be patient ... the Experts here all work "real" jobs, and often must attend to their real-life duties.

Access is a database platform, which means you'll need to "normalize" the database structure in order to reduce issues like redundancy, etc. From what I can see, you need a couple of tables:

tblProjects
---------------
ProjectsID
ProjectName
Description
Department
StartDate
ClientID
etc etc

tblClients
-------------
ClientID
ClientName
ClientPhone
etc

tblDepartments
--------------------
DepartmentID
Department
etc etc

From there, I'm not sure what else is needed to normalize the structure, since I don't know what data your columns contain. I don't need to know the "data type" (like String, Numeric, etc) - I need to know what "QS" means, for example, as well as "36MPS", "12MPS", etc.

Author

Commented:
Hi ,

Thanks for the emaii. Basically QS means the Quote Status and two categories were mentioned
36MPS and 12MPS refers to the professional services for 36 months and 12 months respectively .

Regards,
Aaron
Most Valuable Expert 2012
Top Expert 2014

Commented:
What about the other columns? We don't know your data, so in order to help you you'll have to tell us the meaning of all columns.

Author

Commented:
Hi ,
Below is the details of each column
ID#: Project ID
Client: Which Client
Description : Description of the Project
Revenue :Value of the Project
Cloing Date : Closing Date of the Project
FW : Which category does this project belong to , which can  be  selected by pull down menu
Dept : Which dept does this project belong to
12MPS, 36MPS : Professional Services value for 12 months and 36 months
Finally last two columns will be notes / comments etc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial