Hi,
When you do a JOIN; Where is the new JOINED data stored? Is it stored in memory somewhere or I remember reading about SYS objects. Does it become a SYS object?
What is a JOINs scope/lifespan?
TIA
Hi,
When you do a JOIN; Where is the new JOINED data stored? Is it stored in memory somewhere or I remember reading about SYS objects. Does it become a SYS object?
What is a JOINs scope/lifespan?
TIA
joins are in queries. you can also define joins in relationships, but these are to set the rules of relationships, they do not define how a query has to work. In both cases they last for as long as they are designed - in principle a relationship is for the life of the db, a join for the life of the query. A basic newbie mistake is to assume that queries have to be based on relationships so they 'over relate' the db design.
Lets say a relationship between customers and invoice headers is defined as 'one to many' - one customer can have many invoices but an invoice can only have one customer. This would be represented by an inner join between the two tables in both relationships and queries with a 1 and infinity design at each end of the join.
But if you wanted to find which customers did not have any invoices, you would need to use a left join rather than the inner join as specified in the relationship.
And sometimes there is no relationship as such - for example a table of invoices and a table of financial years - in this case as said there is no relationship between the two tables but they can still be 'joined' in a query as invoice date between start of financial year and end of financial year - but note the this join cannot be represented in the query grid, only in sql - the query grid can only go so far in representing what can be done in sql. However per your previous post re Cartesian queries, you could represent it as Cartesian query and use criteria to 'make' the join. But this will be slower with large datasets. e.g.
cannot be represented in the query grid but will be faster than the cartesian option which can be represented in the query gridCode:SELECT * FROM tblinvoices INNER JOIN tblFinancialYears ON tblInvoices.invdate between tblFinancialYears.startdate and tblFinancialYears.enddate
In the relationships window , right click on a relationship, note the options. Then do the same in a query, you will see they are different.Code:SELECT * FROM tblinvoices , tblFinancialYears WHERE tblInvoices.invdate between tblFinancialYears.startdate and tblFinancialYears.enddate
Haha...
Yeah thats the problem.But this will be slower with large datasets
Its slow & they want to be able to update & maintain it. They are killing this beloved program & trying to convert it to SAS. They hired me saying they were looking for a VBA developer. I guess nobody here knows VBA "its a legacy language". I find that hard to believe. I think nobody want to be bothered. Anyway I am grateful for the opportunity. But I think they needed a DB architect. But while I am here keeping the seat warm I will do the BEST I can for them. lol probably going to get fired tomorrow. lol..... Nah I got through the VBA part of quickly, but these queries are killing me.
Yeah Cartesian queries may not have been the best route. Some people like to over complicate things in an effort to show somebody else how smart they are. I don’t think whoever built this was worried about speed. The DB isnt split. Being a novice that would have been one of the 1st things I would have done. But like I said before they aren’t interested in keeping this thing alive anymore & its days are numbered. #SADFACE
Welp thanks for the info on JOIN & its scope. I really appreciate you taking the time to help me out.
Sorry for the late reply, but I really needed a break. Time to burn the midnight oil.
All the best; see you later (hopefully not again tonight lol..) have a goody, goody.
~Noob
VBA is only a programming language. As you know, you still need a well engineered database. Having said that, you also need a well thought out application architecture. Are you trying to automate a business process or are you trying to revamp an application that provides business analytics?
I am not an expert on SAS. However, I think their solutions are geared more around big data and analytics. So even if they choose some of the products SAS has to offer, it is likely there will still be needs for workgroup applications that Access is well suited for.
access can be as fast as any other db - all depends on how well the tables, relationships and indexes are designed. Moving a process to another db purely because of the belief it will be faster will usually prove to be disappointing - unless of course the opportunity is taken to positively revamp tables, relationships and indexes.
If you are still looking for speed enhancements, check the indexing for each table to ensure it is adequate.
All fields which are used in joins and/or regularly used for filtering on should be indexed unless the field either contains primarily null values or a limited range of values such as Boolean fields.
Also be aware that 'finding' a record in a table or query is significantly slower the filtering. Reason is that 'finding' uses a sequential search whilst filtering uses indexes (if available). So if your uses are 'finding' records, time for some retraining.
And if you are filtering a text field using like '*something' or like '*something*' then the initial * forces access to use a sequential search and abandon the indexes. Some db systems solve this by not allowing the initial *
Hi @ItsMe
Yeah it's a business process.Are you trying to automate a business process or are you trying to revamp an application that provides business analytics?
Yup thats about all I know about SAS too. Trying to make the time to learn it though (Along with a whole bunch of other languages too. (Is SAS even a language? I dont know somebody was tellin me something. lol... ))I am not an expert on SAS. However, I think their solutions are geared more around big data and analytics.
Good I wasnt pleased when they told me what they wanted to do. I am so greatful for little applications like this one.it is likely there will still be needs for workgroup applications that Access is well suited for.
"If an Access database works its way up to the department or enterprise level, celebrate its triumph instead of trashing its creator"
I wanted to fix it or optimize it, but they were like no thats not why you are here.
P.S. Sorry about the late reply. Drama at the office & what not. Everything has settled down now. I told that that Access Queries are as much VBA as a tommato is a vegetable. They took me off it. I am going to try to cook up a surprise though for them on the next DB they just gave me. I learned a lot while I was doing them (queies) & it helped me better understand the code when I just reviewed my notes yesterday.
Hey.... @Ajax,
Yeah I agree but I am just a small cog over here at Spacely Spockets & a Temp at that.access can be as fast as any other db - all depends on how well the tables, relationships and indexes are designed. Moving a process to another db purely because of the belief it will be faster will usually prove to be disappointing - unless of course the opportunity is taken to positively revamp tables, relationships and indexes.
If they decide to keep me around maybe I will get another crack at it if the SAS team fails.If you are still looking for speed enhancements, check the indexing for each table to ensure it is adequate.
Good info #Noted# thanks for that. Also too on that other post thanks too. Because it was your comment to put it on 1 line that got my head out of my butt. It was all 1 long Query string like normal. I did some formating manipulation to make it more digestable, but then I was scared to touch it after that & own it or make the format my own. Anyway that reminder was huge THANKS!!!Also be aware that 'finding' a record in a table or query is significantly slower the filtering. Reason is that 'finding' uses a sequential search whilst filtering uses indexes (if available). So if your uses are 'finding' records, time for some retraining.
And if you are filtering a text field using like '*something' or like '*something*' then the initial * forces access to use a sequential search and abandon the indexes. Some db systems solve this by not allowing the initial *
It is possible to incorporate VBA functions in Access queries. But yeah, it is still SQL and when you are working in a VBA module, you need to pass SQL to objects as a string.