Convert Excel into Access Database (1 Viewer)

[itsjamaal]

I would like to convert my Excel into an Access database.

I am attaching the file here. Please guide me to have a well-arranged Access database.

 

[CarlettoFed]

First you should tell what the List, Amount, Currency, Accounts2 columns are for.

 

[ebs17]

a well-arranged Access database

... must be rebuilt starting with the database schema.
There is no conversion.

 

[plog]

The process of setting up data in a database is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

Give that link a read, then google for a few tutorials, then apply what you learn to your data in an Access file. Post that file back here and we can go over it with you to make sure you have properly set it up.

 

[The_Doc_Man]

You have gotten some good advice already. I will add this.

It's not REALLY a "secret" but the secret of a "well-arranged Access database" is to spend time up-front in doing design and analysis work to determine requirements. That is: What do you have to work with (data source questions)? What do you expect from the database (report and display questions)? Given your other findings, how will you get from input to results (computation and processing questions)? How will you put all these things together (integration questions)? What do your application users need to know/understand and what will they see (interface questions)? If you do this right, you will swear you have just been brutally interrogated - by yourself. And yes, sometimes it can feel that way.

We can guide you but you need to ask narrowly targeted questions. Since we don't know your business or your local regulations, we will be good technical guides but poor business guides. Just remember, many of us are literally a continent away. You are in Dubai whereas I am in the southern part of the USA near the Gulf of Mexico. So we are unlikely to know your specific situation. Therefore, any questions you ask must be asked based on the assumption that we have no idea what you are doing.

 

[itsjamaal]

Now I have created some tables.
I need to create a form for "Transactions"
When I select the 'trans_type', then 'trans_category' should be filtered based on that selected value 'trans_subcategory' should be filtered.
Guide me to achieve this.
I will add my questions once I am done with the above requirements.

 

[Gasman]

With the phrase 'A picture is worth a thousand words' there are plenty of videos on YouTube

https://www.youtube.com/results?search_query=access+database

especially

https://www.youtube.com/results?search_query=access+database+normalization

 

[itsjamaal]

Now I have created some tables.
I need to create a form for "Transactions"
When I select the 'trans_type', then 'trans_category' should be filtered based on that selected value 'trans_subcategory' should be filtered.
Guide me to achieve this.
I will add my questions once I am done with the above requirements.


Private Sub trans_type_AfterUpdate()
Me.trans_category.RowSource = "SELECT category_name, category_id FROM category WHERE trans_type = " & Me.trans_type & " ORDER BY category_name;"
Me.trans_category.Requery
End Sub
Private Sub trans_category_AfterUpdate()
Me.trans_subcategory.RowSource = "SELECT subcategory_name, subcategory_id FROM subcategory WHERE category_id = " & Me.trans_category & " ORDER BY subcategory_name;"
Me.trans_subcategory.Requery
End Sub

----------------

By this, that job is done.
I have 'multi' trans_mode, before I enter any new transactions, I need to check the balances, based on every trans_mode.
I am also searching on youtube also in net, if I found out there will update here.

 

[itsjamaal]


Private Sub trans_type_AfterUpdate()
Me.trans_category.RowSource = "SELECT category_name, category_id FROM category WHERE trans_type = " & Me.trans_type & " ORDER BY category_name;"
Me.trans_category.Requery
End Sub
Private Sub trans_category_AfterUpdate()
Me.trans_subcategory.RowSource = "SELECT subcategory_name, subcategory_id FROM subcategory WHERE category_id = " & Me.trans_category & " ORDER BY subcategory_name;"
Me.trans_subcategory.Requery
End Sub

----------------

By this, that job is done.
I have 'multi' trans_mode, before I enter any new transactions, I need to check the balances, based on every trans_mode.
I am also searching on youtube also in net, if I found out there will update here.


=DLookup("Sum(trans_amount)", "transactions", "trans_mode = '" & [trans_mode] & "'")


I have added a text box next to trans_mode, when I select that, it shows the current balance of that trans_mode.
Now I will check on reporting, Thanks in advance for all the guidance given.

 

[Uncle Gizmo]

This is a common route in to Access Database.


This blog maybe of interest:-

Excel in Access

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[plog]

The step after tables is not forms, it's Reports and the queries to achieve the reports. Doing that next helps you test that the tables and relationships you have set up are appropriate for what you need. Spoiler alert, they are not. So before we move on we need to address the major issue with your tables -- too many relationships.

If you open your Relationship Tool in the database you posted you will see that just about every table is connected to every other table. This is wrong, there should only be one way to travel from one table to another. I can draw 3 paths from transactions to category (1 direct, 2 by going through the other tables). You need to decide what tables are actually directly related and remove all the other relationships. My guess is that trans_type should only directly be connected to transactions, category should only be directly connected to subcategory and subcategory should then be directly connected to transactions.

Additionally, I would add a type_value to trans_type to designate if a transaction was a credit (+1) or a debit (-1).

 

[Pat Hartman]

Your naming standards could use some adjustment. Using a prefix, especially a long prefix only impedes viewing and intellisense. For example, you need to type subcategory_ before intellisense will kick in. When you open the table or a query in ds view, all you'll see for names for EVERY column is "subcatego".

Also, for sanity's sake, naming the FK's with the same name as the PK they point to will avoid lots of confusion .