Results 1 to 8 of 8
  1. #1
    Noob's Avatar
    Noob is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    36

    Question Silly Questions about JOIN

    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

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

    Code:
    SELECT *
    FROM tblinvoices INNER JOIN tblFinancialYears ON tblInvoices.invdate between tblFinancialYears.startdate and tblFinancialYears.enddate
    cannot be represented in the query grid but will be faster than the cartesian option which can be represented in the query grid

    Code:
    SELECT *
    FROM tblinvoices , tblFinancialYears 
    WHERE 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.

  3. #3
    Noob's Avatar
    Noob is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    36
    Haha...
    But this will be slower with large datasets
    Yeah thats the problem.

    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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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 *

  6. #6
    Noob's Avatar
    Noob is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    36
    Hi @ItsMe

    Are you trying to automate a business process or are you trying to revamp an application that provides business analytics?
    Yeah it's a business process.

    I am not an expert on SAS. However, I think their solutions are geared more around big data and 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... ))

    it is likely there will still be needs for workgroup applications that Access is well suited for.
    Good I wasnt pleased when they told me what they wanted to do. I am so greatful for little applications like this one.
    "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.

  7. #7
    Noob's Avatar
    Noob is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    36
    Hey.... @Ajax,

    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.
    Yeah I agree but I am just a small cog over here at Spacely Spockets & a Temp at that.

    If you are still looking for speed enhancements, check the indexing for each table to ensure it is adequate.
    If they decide to keep me around maybe I will get another crack at it if the SAS team fails.

    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 *
    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!!!

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create an error on Illogical (Silly) dates!?
    By gangel in forum Programming
    Replies: 4
    Last Post: 09-07-2015, 08:38 AM
  2. Silly Macro Question
    By Stretholox in forum Macros
    Replies: 12
    Last Post: 01-07-2015, 01:14 PM
  3. Replies: 3
    Last Post: 01-20-2013, 01:14 AM
  4. Silly error on a simple function call - help
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 5
    Last Post: 06-10-2011, 01:23 PM
  5. Silly question
    By HelenP in forum Forms
    Replies: 1
    Last Post: 11-23-2010, 11:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums