Access database help
Discussion
I'm new to DB's so hopefully this is a really easy problem that I just don't know how to do.
I have a list of tasks, some of them are subtasks of others on the list, and some are sub tasks of sub tasks.
I can built a table of the parent child relationship, eg
How would I then query that to show that Task A includes Tasks B, C, D and E, while F includes C and D?
Ideally I want to be able to produce an output something like
I have a list of tasks, some of them are subtasks of others on the list, and some are sub tasks of sub tasks.
I can built a table of the parent child relationship, eg
| Parent | Child |
|---|---|
| A | B |
| B | C |
| C | D |
| A | E |
| F | C |
How would I then query that to show that Task A includes Tasks B, C, D and E, while F includes C and D?
Ideally I want to be able to produce an output something like
| A | B | C | D |
| A | E | ||
| F | C | D |
Have a look at this tutorial: https://www.experts-exchange.com/articles/2153/Man...
Typed a long reply but got deleted :S
I think you need to restructure your table design. If you want a hierachy, i.e. where a task have have multiple sub tasks and each sub tasks can have their own sub-sub tasks you need to consider using Foreign Keys.
As quick summary your main task table needs to have a unique ID. Your sub task table would also have a foreign key which is the main task tables ID.
Are you using Access tables? From experience I'd recommend using SQL if possible, less headache if you plan to scale.
edit: looks like the forum doesn't like me inserting SQL code...
You would need to use inner [j][o][i][n] in your query, joined on the Foreign Keys
I think you need to restructure your table design. If you want a hierachy, i.e. where a task have have multiple sub tasks and each sub tasks can have their own sub-sub tasks you need to consider using Foreign Keys.
As quick summary your main task table needs to have a unique ID. Your sub task table would also have a foreign key which is the main task tables ID.
Are you using Access tables? From experience I'd recommend using SQL if possible, less headache if you plan to scale.
edit: looks like the forum doesn't like me inserting SQL code...
You would need to use inner [j][o][i][n] in your query, joined on the Foreign Keys
Edited by anxious_ant on Wednesday 21st October 12:46
Edited by anxious_ant on Wednesday 21st October 12:47
RizzoTheRat said:
I'm new to DB's so hopefully this is a really easy problem that I just don't know how to do.
I have a list of tasks, some of them are subtasks of others on the list, and some are sub tasks of sub tasks.
I can built a table of the parent child relationship, eg
How would I then query that to show that Task A includes Tasks B, C, D and E, while F includes C and D?
Ideally I want to be able to produce an output something like
I’ve done a quick fiddle for you, but I’m not 100% sure it matches what you hope your results to look like I have a list of tasks, some of them are subtasks of others on the list, and some are sub tasks of sub tasks.
I can built a table of the parent child relationship, eg
| Parent | Child |
|---|---|
| A | B |
| B | C |
| C | D |
| A | E |
| F | C |
How would I then query that to show that Task A includes Tasks B, C, D and E, while F includes C and D?
Ideally I want to be able to produce an output something like
| A | B | C | D |
| A | E | ||
| F | C | D |
Anyway, have a look .. http://sqlfiddle.com/#!18/fd6a0/10
Given my results versus your “something like” grid, do you want my rows 2 and 3 to be included?
Edited by bigandclever on Wednesday 21st October 13:04
bigandclever said:
Let’s have a look then
Pretty much as you and Troubleatmill suggested, however it seems the forum won't let me post SQL code.LEFT JOIN from child to parent on 5 instances of the same table using different alias names.
I'm then using an IIF statement to generate another column of the lowest level task from each line, in a column called LastTask.
However I'd then like to link another table of task and equipment to the LastTask column, but if I try to
LEFT JOIN [Kit] ON LastTask = [Kit].[Task]
it doesn't work. Is it possible to join to a column generated by an IIF? Or is there a better way to determine the furthest right task in this structure?
Boringly you could do a create_table as blah using whatever your first bit of sql is. And then do your next bit of sql against blah. Given where you are, I’d suggest this more basic approach because it lets you verify the data in stages, so you’re only unpicking one piece at a time.
Then start getting funky with subqueries and making things more elegant.
Then start getting funky with subqueries and making things more elegant.
RizzoTheRat said:
Is there a way to do the same thing in SQL? Obvious way in Access to have a separate query but it must be possible to do all in one.
Yes, however I'd admit it's been couple of years since I did Access dev. Sounds like you are using the Access Query editor?How many users would use this DB? Do you know how large it would potentially be? Access is ok for low number of users, I would say no more than 5 concurrent on local network. Otherwise you might run into file locking issues.
Have a look at the link below, and see if it helps. I would also advise restructuring your tables. The table structure doesn't really suit what you describe as the spec.
http://www.fmsinc.com/MicrosoftAccess/query/index....
Edited by anxious_ant on Friday 23 October 07:56
Edited by anxious_ant on Friday 23 October 07:57
Local network file, one user at a time, fairly small data set (6 or 8 different sets of 50 or so records, and another couple of tables of similar size that have linked records. Mainly in the Access query editor but I know a tiny bit of SQL so tweaking it in SQL view as well.
Currently we do way too much in Excel and I'm using this as a bit of a learning experience on Access and SQL while I look at where there might be inconsistencies in the data. To be honest I could have done it all in Excel in a fraction of the time but I think having it as a database is going to make updating records a lot easier.
I'll have another play with a couple of those suggestions later, cheers. I think structuring the data correctly in the first place is the main thing, in the original spreadsheets its laid out like this which is a nightmare to work with
I've converted it to a parent-child list which seems to make sense, but then there are differences between some of the different data sets...
Basically I'm having a bit of a play at the moment to see what I can do with it.
Currently we do way too much in Excel and I'm using this as a bit of a learning experience on Access and SQL while I look at where there might be inconsistencies in the data. To be honest I could have done it all in Excel in a fraction of the time but I think having it as a database is going to make updating records a lot easier.
I'll have another play with a couple of those suggestions later, cheers. I think structuring the data correctly in the first place is the main thing, in the original spreadsheets its laid out like this which is a nightmare to work with
| A | ||
| B | ||
| C | ||
| D | ||
| E | ||
| F |
I've converted it to a parent-child list which seems to make sense, but then there are differences between some of the different data sets...
Basically I'm having a bit of a play at the moment to see what I can do with it.
Edited by RizzoTheRat on Friday 23 October 08:48
RizzoTheRat said:
Local network file, one user at a time, fairly small data set (6 or 8 different sets of 50 or so records, and another couple of tables of similar size that have linked records. Mainly in the Access query editor but I know a tiny bit of SQL so tweaking it in SQL view as well.
Currently we do way too much in Excel and I'm using this as a bit of a learning experience on Access and SQL while I look at where there might be inconsistencies in the data. To be honest I could have done it all in Excel in a fraction of the time but I think having it as a database is going to make updating records a lot easier.
I'll have another play with a couple of those suggestions later, cheers. I think structuring the data correctly in the first place is the main thing, in the original spreadsheets its laid out like this which is a nightmare to work with
I've converted it to a parent-child list which seems to make sense, but then there are differences between some of the different data sets...
Basically I'm having a bit of a play at the moment to see what I can do with it.
Good luck Currently we do way too much in Excel and I'm using this as a bit of a learning experience on Access and SQL while I look at where there might be inconsistencies in the data. To be honest I could have done it all in Excel in a fraction of the time but I think having it as a database is going to make updating records a lot easier.
I'll have another play with a couple of those suggestions later, cheers. I think structuring the data correctly in the first place is the main thing, in the original spreadsheets its laid out like this which is a nightmare to work with
| A | ||
| B | ||
| C | ||
| D | ||
| E | ||
| F |
I've converted it to a parent-child list which seems to make sense, but then there are differences between some of the different data sets...
Basically I'm having a bit of a play at the moment to see what I can do with it.
Edited by RizzoTheRat on Friday 23 October 08:48
MS Access is a good learning tool to begin exploring databases. From your use case Access would work.Probably gives you more control and easier to implement any business logic.
Transitioning from Excel to database might not always be straight forward, but best to ensure you get the data structures right from the start.
Don't try to replicate exactly what you have in Excel, rather have a think of how to structure the data in a meaning manner in the database.
It is also possible to use Access as frontend and link to SQL database (MS SQLExpress is free for small dbs). I find it easier to work with data in SQL compared to Access RDMS but each to their own
I think there is a sample Task list management database in MS Access which you can have a look at as a guide. Or you can search similiar templates online.
If you are joining to X number of tables you will be limiting yourself if you ever go any levels deeper than your query allows - you really need some sort of hierarchy or recursive type query to expand out to N levels where N can be variable.
However Access isn't really natively that great at that!
Do a search or Recursive or Hierarchy queries as some folks have come up with some clever ideas.
However Access isn't really natively that great at that!
Do a search or Recursive or Hierarchy queries as some folks have come up with some clever ideas.
Cheers. I did an online intro to SQL thing a while back (W3Schools i think) so only really skimmed the surface, I find the builder in access is pretty useful as I can do stuff in multiple steps and check the outputs as I go, plus start to get a better understanding of SQL by what Access generates (although accepting it's probably about the same level of quality/efficiency as recorded VBA macros in Excel 
)
We do have some stuff in SQL server at work but mostly we overuse Excel and VBA so I feel it's something that's worth learning and trying to use a bit more. I find playing with something I actually want to get useful information out of is a much better way to learn than tutorials/courses that don't relate to my requirements, but means I can easily end up doing things in a very inefficient way.
) We do have some stuff in SQL server at work but mostly we overuse Excel and VBA so I feel it's something that's worth learning and trying to use a bit more. I find playing with something I actually want to get useful information out of is a much better way to learn than tutorials/courses that don't relate to my requirements, but means I can easily end up doing things in a very inefficient way.
If you're trying to model the data and it's as simple as the setup you've described then you should have a look at PowerPivot in Excel rather than Access. No one uses Access.
Excel will let you setup the data, define relationships between tables and it will let you test your setup simply by dropping columns into a Pivot Table - If you can draw the table you want without it complaining about missing relationships, then you're probably OK. Then check the numbers are right and you're done.
From your description, you could just carry on with Excel from that point as it will do what you want perfectly fine, without having to resort to formulas, let alone VBA. You can then do complex querying of the data model with DAX, which I think is better than SQL in many way when it comes to actually analysing your data.
If you need a server-based solution I'd be looking at setting up an MS SQL Server simply because it'll let you then run Analysis Services. You can then have your model in Excel or PowerBI and let that do all the querying of the database rather than messing around with SQL. If you have a budget for this I'd be tempted to set it up on Azure rather than messing around with hardware.
On that note, you can signup for a free trial on Azure and get it all working at no cost so you can see how simple it can be.
Excel will let you setup the data, define relationships between tables and it will let you test your setup simply by dropping columns into a Pivot Table - If you can draw the table you want without it complaining about missing relationships, then you're probably OK. Then check the numbers are right and you're done.
From your description, you could just carry on with Excel from that point as it will do what you want perfectly fine, without having to resort to formulas, let alone VBA. You can then do complex querying of the data model with DAX, which I think is better than SQL in many way when it comes to actually analysing your data.
If you need a server-based solution I'd be looking at setting up an MS SQL Server simply because it'll let you then run Analysis Services. You can then have your model in Excel or PowerBI and let that do all the querying of the database rather than messing around with SQL. If you have a budget for this I'd be tempted to set it up on Azure rather than messing around with hardware.
On that note, you can signup for a free trial on Azure and get it all working at no cost so you can see how simple it can be.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff