Guidance needed for a text database project please
- Thread starter Vitesse
- Start date
Access '97 says "Unrecognised database format" :-( I've got Office 2007 on my home PC I'll see if I can see it on that machine. Sorry about that - one of my limitations is that I can only work on the thing in Access 97.
That works great thanks! I think what I needed was something to work on, so big thank you for that
Now to get to understand how the db works and what I can do with it - this is so much better than the one I tried to do!
OK so I guess the next step is to put the existing data into the db by importing the spreadsheet? Then I can figure out what needs to change in that collection sheet - tricky as it's already in use but it's not impossible, I think it's a "phase 2" thing
Then I need to get into how to report dealw ith updates and new data sheets.
If I'm doing things in the wrong order tell me cause I'm now officially out of my depth
Here's the one complete spreadsheet I have, although there seems to be a link between Owner and Location I want to keep it unlinked because those owners can and will change.
Rite well you can easily hit 'two birds with one stone' in what you want to do above. Where you say you want to import data and work out how to update data this can be done in a single process. What you need to do Is create a Loop. Baisically you would import the data every week/month etc.. into a temp table & then have a number of loops to search through your exsisting tables and where the record does not exsist it will add it to the system. I will dig out a script and post it up here in a little bit just abit busy atm... as for reports they are just pretty queries so no problems there.. 
i will post back with a script in a cuple hours!
i will post back with a script in a cuple hours!Thanks for that rsmonkey - I've always had a bit of a blind spot on how to start doing these things especially when it comes to setting up tables. I always want to put everything in one big table. I can see that splitting it down is better BUT it's the relationships bit that I can't quite get my head around - the primary key thing just doesn't fit with my legic but I think I'm getting there. If we get the relationships between tables right then create a quesry to put them all back together to look like one big table then I can import my data into that and the relationships will be fine.
Sorry if I'm rambling, I have picked up some sort of flu so not thinking really clearly atm :-(
I'm going to play now and see what I can come up with
Sorry if I'm rambling, I have picked up some sort of flu so not thinking really clearly atm :-(
I'm going to play now and see what I can come up with
mdschuetz
Nerd Incognito
- Local time
- Today, 15:10
- Joined
- Oct 31, 2007
- Messages
- 49
Updated
Check it out. From this point, I think you have something to work with. Browse around for information on how to import spreadsheet data into tables. Its really not that hard once you do it a couple times. There are some really smart people here who can help you with VBA to add functionality specific to a task you wish to automate. I am pretty new at this myself, anyhow enjoy.
The reason for breaking stuff down into tables is doing so elimates repetative data which keeps the database smaller. You see, tblJobs, instead of holding the name of each key deliverable, action, or benefits incorporated instead holds a one or two digit integer which relates to the name of each deliverable, action, or benefits incorporated. That drastically reduces the size of your data you have stored.
For instance....
'Compliance SA - staff files checked and results recorded. Any errors identified are corrected. Evidence on file to prove competency and all correct documentation included' is recorded in tblAct with an identifier of '16'
is recorded as '16' in tblJob
That means you only duplicate 2 characters in comparison to 229 characters.
Check it out. From this point, I think you have something to work with. Browse around for information on how to import spreadsheet data into tables. Its really not that hard once you do it a couple times. There are some really smart people here who can help you with VBA to add functionality specific to a task you wish to automate. I am pretty new at this myself, anyhow enjoy.
The reason for breaking stuff down into tables is doing so elimates repetative data which keeps the database smaller. You see, tblJobs, instead of holding the name of each key deliverable, action, or benefits incorporated instead holds a one or two digit integer which relates to the name of each deliverable, action, or benefits incorporated. That drastically reduces the size of your data you have stored.
For instance....
'Compliance SA - staff files checked and results recorded. Any errors identified are corrected. Evidence on file to prove competency and all correct documentation included' is recorded in tblAct with an identifier of '16'
is recorded as '16' in tblJob
That means you only duplicate 2 characters in comparison to 229 characters.
Last edited:
Is there any facility here to do stuff live - like a chat client? I'm thinking that if someone could push me in the right direction then I might get a bit better at this, feel like I'm absorbing you guys' time but not making much progress
I would gladly give you my msn or summin but it's restricted at work and i doubt very much whether you would want to do this in your spare time.. anyways forums give you time to research stuff so its a better medium of learning
And the idea of storing everything in one big table is not logical I know you've been refered to Normalisation but by storing data in multiple talbes it prevents duplication. Ideally in a database NOTHING should be repeated. I.E Names, Actions, Stuff etc.. baisically its breaking everything down into its sub elements. Say we're doing a db based on peoples shopping lists rather than having a big table which says:
James: Apples, Pears, Curry, Cheese
Tom: Apples, Pears, Curry, Cheese
Bill: Apples, Pears, Curry, Cheese
You would potentially have 4 tables: Name, Fruit, Ready Meals, Dairy Products.
With the idea that each item has a primary key attached you would then have a 5th table named lets say Shopping List. This would look something liek this:
1, 1, 2, 1, 1
2, 1, 2, 1, 1
3, 1, 2, 1, 1
Relationships would then be made from this Shopping List table to the other four tables. They would be connected by their unique identifiers (primary keys/foreign keys).
These represent the unique identifiers of the items mentioned above. Ergo you have prevented duplication and maintained efficiency. Although not noticable in a small DB's in big Db's (i.e. having 100,000 records) you would notice a much improved performance over a Db without this style of formatting.
Anyway thats stuff im sure you've already looked at and started to implement within your Db. I havent looked but im sure mdschuetz Db conforms to these rules to look through it, get familiar with the table structure's, look through the forms control properties and have a gander at the vba code and try to get a feel for the VB style and structure of coding. Remember VB is a procedual language which means that it does process code from top to bottom of the page.. unless you tell it otherwise!
anywhooo getting off the point.
1. Do you want to browse folders and import the excel file or will it be in a fixed directory and you just wanna go into access and press a button & it'l import that file?
2. This will import into a single Temp file which although is held within access is not actually a working organ of this Db rather a data storage device for us to manipulate.
3. So this is where loops come in the frey. As i said before and the above info should help, but depending on howmany tables you have will directly relate to how many loops you will need. Heres an framework for a loop:
apologies if their is any bad language in thier i had to lift that from one of me Db's and just quickly changed the iteam names but i usually leave loadsa swearing comments in cause i've got a short attention span!
Rite so looking at this code I hope it sheds some more light on what i was on about in previous posts. Loops such as this can be stored within modules so that they can then be manipulated easily via forms:
Thus code within a form can be minimalised. Of and the code above means that when you press the button name 'Command1' it will Execute module named 'LoopExample'..
I'll let you digest this then we can go further.....
And the idea of storing everything in one big table is not logical I know you've been refered to Normalisation but by storing data in multiple talbes it prevents duplication. Ideally in a database NOTHING should be repeated. I.E Names, Actions, Stuff etc.. baisically its breaking everything down into its sub elements. Say we're doing a db based on peoples shopping lists rather than having a big table which says:
James: Apples, Pears, Curry, Cheese
Tom: Apples, Pears, Curry, Cheese
Bill: Apples, Pears, Curry, Cheese
You would potentially have 4 tables: Name, Fruit, Ready Meals, Dairy Products.
With the idea that each item has a primary key attached you would then have a 5th table named lets say Shopping List. This would look something liek this:
1, 1, 2, 1, 1
2, 1, 2, 1, 1
3, 1, 2, 1, 1
Relationships would then be made from this Shopping List table to the other four tables. They would be connected by their unique identifiers (primary keys/foreign keys).
These represent the unique identifiers of the items mentioned above. Ergo you have prevented duplication and maintained efficiency. Although not noticable in a small DB's in big Db's (i.e. having 100,000 records) you would notice a much improved performance over a Db without this style of formatting.
Anyway thats stuff im sure you've already looked at and started to implement within your Db. I havent looked but im sure mdschuetz Db conforms to these rules to look through it, get familiar with the table structure's, look through the forms control properties and have a gander at the vba code and try to get a feel for the VB style and structure of coding. Remember VB is a procedual language which means that it does process code from top to bottom of the page.. unless you tell it otherwise!
anywhooo getting off the point.
1. Do you want to browse folders and import the excel file or will it be in a fixed directory and you just wanna go into access and press a button & it'l import that file?
2. This will import into a single Temp file which although is held within access is not actually a working organ of this Db rather a data storage device for us to manipulate.
3. So this is where loops come in the frey. As i said before and the above info should help, but depending on howmany tables you have will directly relate to how many loops you will need. Heres an framework for a loop:
Code:
Public Sub LoopExample()
Dim DB As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set DB = CurrentDb()
strSQL = "SELECT whatever FROM Temp;" <-- (SQL statement to select whatever you need to insert from your temp table)
Set rs = DB.OpenRecordset(strSQL)
If Not rs.EOF Then
Do While Not rs.EOF
strWhatever = rs!Whatever
strSQL = "SELECT Real_Table.Item FROM Real_Table WHERE Real_Table.Item = '" & strWhatever & "'"
Set rs2 = DB.OpenRecordset(strSQL)
If Not rs2.EOF Then
'do nothing
Else
strSQL = "INSERT INTO Real_Table ( Item ) VALUES ( '" & strWhatever & "' )"
CurrentDb.Execute (strSQL)
End If
rs2.Close
Set rs2 = Nothing
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
CurrentDb.Execute (UPDATE SQL statement can go here if required to fill information refering to the item above)
MsgBox "Complete"
Set DB = Nothing
End Subapologies if their is any bad language in thier i had to lift that from one of me Db's and just quickly changed the iteam names but i usually leave loadsa swearing comments in cause i've got a short attention span!
Rite so looking at this code I hope it sheds some more light on what i was on about in previous posts. Loops such as this can be stored within modules so that they can then be manipulated easily via forms:
Code:
Private Sub Command1_Click()
Call LoopExample
End SubThus code within a form can be minimalised. Of and the code above means that when you press the button name 'Command1' it will Execute module named 'LoopExample'..
I'll let you digest this then we can go further.....
I would gladly give you my msn or summin but it's restricted at work and i doubt very much whether you would want to do this in your spare time.. anyways forums give you time to research stuff so its a better medium of learning
And the idea of storing everything in one big table is not logical I know you've been refered to Normalisation but by storing data in multiple talbes it prevents duplication. Ideally in a database NOTHING should be repeated. I.E Names, Actions, Stuff etc.. baisically its breaking everything down into its sub elements. Say we're doing a db based on peoples shopping lists rather than having a big table which says:
James: Apples, Pears, Curry, Cheese
Tom: Apples, Pears, Curry, Cheese
Bill: Apples, Pears, Curry, Cheese
You would potentially have 4 tables: Name, Fruit, Ready Meals, Dairy Products.
With the idea that each item has a primary key attached you would then have a 5th table named lets say Shopping List. This would look something liek this:
1, 1, 2, 1, 1
2, 1, 2, 1, 1
3, 1, 2, 1, 1
Relationships would then be made from this Shopping List table to the other four tables. They would be connected by their unique identifiers (primary keys/foreign keys).
These represent the unique identifiers of the items mentioned above. Ergo you have prevented duplication and maintained efficiency. Although not noticable in a small DB's in big Db's (i.e. having 100,000 records) you would notice a much improved performance over a Db without this style of formatting.
Anyway thats stuff im sure you've already looked at and started to implement within your Db. I havent looked but im sure mdschuetz Db conforms to these rules to look through it, get familiar with the table structure's, look through the forms control properties and have a gander at the vba code and try to get a feel for the VB style and structure of coding. Remember VB is a procedual language which means that it does process code from top to bottom of the page.. unless you tell it otherwise!
anywhooo getting off the point.
1. Do you want to browse folders and import the excel file or will it be in a fixed directory and you just wanna go into access and press a button & it'l import that file?
2. This will import into a single Temp file which although is held within access is not actually a working organ of this Db rather a data storage device for us to manipulate.
3. So this is where loops come in the frey. As i said before and the above info should help, but depending on howmany tables you have will directly relate to how many loops you will need. Heres an framework for a loop:
apologies if their is any bad language in thier i had to lift that from one of me Db's and just quickly changed the iteam names but i usually leave loadsa swearing comments in cause i've got a short attention span!
Rite so looking at this code I hope it sheds some more light on what i was on about in previous posts. Loops such as this can be stored within modules so that they can then be manipulated easily via forms:
Thus code within a form can be minimalised. Of and the code above means that when you press the button name 'Command1' it will Execute module named 'LoopExample'..
I'll let you digest this then we can go further.....
And the idea of storing everything in one big table is not logical I know you've been refered to Normalisation but by storing data in multiple talbes it prevents duplication. Ideally in a database NOTHING should be repeated. I.E Names, Actions, Stuff etc.. baisically its breaking everything down into its sub elements. Say we're doing a db based on peoples shopping lists rather than having a big table which says:
James: Apples, Pears, Curry, Cheese
Tom: Apples, Pears, Curry, Cheese
Bill: Apples, Pears, Curry, Cheese
You would potentially have 4 tables: Name, Fruit, Ready Meals, Dairy Products.
With the idea that each item has a primary key attached you would then have a 5th table named lets say Shopping List. This would look something liek this:
1, 1, 2, 1, 1
2, 1, 2, 1, 1
3, 1, 2, 1, 1
Relationships would then be made from this Shopping List table to the other four tables. They would be connected by their unique identifiers (primary keys/foreign keys).
These represent the unique identifiers of the items mentioned above. Ergo you have prevented duplication and maintained efficiency. Although not noticable in a small DB's in big Db's (i.e. having 100,000 records) you would notice a much improved performance over a Db without this style of formatting.
Anyway thats stuff im sure you've already looked at and started to implement within your Db. I havent looked but im sure mdschuetz Db conforms to these rules to look through it, get familiar with the table structure's, look through the forms control properties and have a gander at the vba code and try to get a feel for the VB style and structure of coding. Remember VB is a procedual language which means that it does process code from top to bottom of the page.. unless you tell it otherwise!
anywhooo getting off the point.
1. Do you want to browse folders and import the excel file or will it be in a fixed directory and you just wanna go into access and press a button & it'l import that file?
2. This will import into a single Temp file which although is held within access is not actually a working organ of this Db rather a data storage device for us to manipulate.
3. So this is where loops come in the frey. As i said before and the above info should help, but depending on howmany tables you have will directly relate to how many loops you will need. Heres an framework for a loop:
Code:
Public Sub LoopExample()
Dim DB As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set DB = CurrentDb()
strSQL = "SELECT whatever FROM Temp;" <-- (SQL statement to select whatever you need to insert from your temp table)
Set rs = DB.OpenRecordset(strSQL)
If Not rs.EOF Then
Do While Not rs.EOF
strWhatever = rs!Whatever
strSQL = "SELECT Real_Table.Item FROM Real_Table WHERE Real_Table.Item = '" & strWhatever & "'"
Set rs2 = DB.OpenRecordset(strSQL)
If Not rs2.EOF Then
'do nothing
Else
strSQL = "INSERT INTO Real_Table ( Item ) VALUES ( '" & strWhatever & "' )"
CurrentDb.Execute (strSQL)
End If
rs2.Close
Set rs2 = Nothing
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
CurrentDb.Execute (UPDATE SQL statement can go here if required to fill information refering to the item above)
MsgBox "Complete"
Set DB = Nothing
End Subapologies if their is any bad language in thier i had to lift that from one of me Db's and just quickly changed the iteam names but i usually leave loadsa swearing comments in cause i've got a short attention span!
Rite so looking at this code I hope it sheds some more light on what i was on about in previous posts. Loops such as this can be stored within modules so that they can then be manipulated easily via forms:
Code:
Private Sub Command1_Click()
Call LoopExample
End SubThus code within a form can be minimalised. Of and the code above means that when you press the button name 'Command1' it will Execute module named 'LoopExample'..
I'll let you digest this then we can go further.....
Similar threads
