By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,795 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,795 IT Pros & Developers. It's quick & easy.

Joining tables in VB via Access using Visdata

P: 62
I have created 9 tables in access via visdata and need to create another table joining all these tables. I have used the query in visdata to do this but it gives me an outrageous view. I have entered 2 records in each table but it gives me 65 records in the query. Please could you help me out. This is the code genreated by Visdata after I created the query, please what is wrong.
the common field in 8 of the tables is Vehicle no and the common field betwwen two other tables is Vehicle id

SELECT H_DUTY_PER.H_DUTY_PER_ISST, H_DUTY_PER.H_DUTY_PER_EXPT, H_PER.H_PER_ISST, H_PER.H_PER_EXPT, INS_CERT.INS_CERT_ISST, INS_CERT.INS_CERT_EXPT, MOB_ADV.MOB_ADV_ISST, MOB_ADV.MOB_ADV_EXPT, RD_WORTH.RD_WORTH_ISST, RD_WORTH.RD_WORTH_EXPT, VEH_DESCRIPTION.VEHICLE_NO, VEH_DESCRIPTION.ENGINE_NO, VEH_DESCRIPTION.CHASIS_NO, VEH_LIC.VEH_LIC_ISST, VEH_LIC.VEH_LIC_EXPT, VEH_TYPE.VEHICLE_TYPE
FROM H_DUTY_PER , H_PER, INS_CERT, MOB_ADV, RD_WORTH, VEH_DESCRIPTION, VEH_LIC, VEH_TYPE
WHERE VEH_DESCRIPTION.VEHICLE_ID=VEH_TYPE.VEHICLE_ID And VEH_DESCRIPTION.VEHICLE_NO=VEH_LIC.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_DUTY_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=INS_CERT.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=MOB_ADV.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=RD_WORTH.VEHICLE_NO;
Nov 16 '06 #1
Share this Question
Share on Google+
2 Replies


100+
P: 1,646
I have created 9 tables in access via visdata and need to create another table joining all these tables. I have used the query in visdata to do this but it gives me an outrageous view. I have entered 2 records in each table but it gives me 65 records in the query. Please could you help me out. This is the code genreated by Visdata after I created the query, please what is wrong.
the common field in 8 of the tables is Vehicle no and the common field betwwen two other tables is Vehicle id

SELECT H_DUTY_PER.H_DUTY_PER_ISST, H_DUTY_PER.H_DUTY_PER_EXPT, H_PER.H_PER_ISST, H_PER.H_PER_EXPT, INS_CERT.INS_CERT_ISST, INS_CERT.INS_CERT_EXPT, MOB_ADV.MOB_ADV_ISST, MOB_ADV.MOB_ADV_EXPT, RD_WORTH.RD_WORTH_ISST, RD_WORTH.RD_WORTH_EXPT, VEH_DESCRIPTION.VEHICLE_NO, VEH_DESCRIPTION.ENGINE_NO, VEH_DESCRIPTION.CHASIS_NO, VEH_LIC.VEH_LIC_ISST, VEH_LIC.VEH_LIC_EXPT, VEH_TYPE.VEHICLE_TYPE
FROM H_DUTY_PER , H_PER, INS_CERT, MOB_ADV, RD_WORTH, VEH_DESCRIPTION, VEH_LIC, VEH_TYPE
WHERE VEH_DESCRIPTION.VEHICLE_ID=VEH_TYPE.VEHICLE_ID And VEH_DESCRIPTION.VEHICLE_NO=VEH_LIC.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_DUTY_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=H_PER.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=INS_CERT.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=MOB_ADV.VEHICLE_NO AND VEH_DESCRIPTION.VEHICLE_NO=RD_WORTH.VEHICLE_NO;
Hi. You might add DISTINCT after SELECT
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.   H_DUTY_PER.H_DUTY_PER_ISST
  3. , H_DUTY_PER.H_DUTY_PER_EXPT
  4. , H_PER.H_PER_ISST
  5. , H_PER.H_PER_EXPT
  6. , INS_CERT.INS_CERT_ISST
  7. , INS_CERT.INS_CERT_EXPT
  8. , MOB_ADV.MOB_ADV_ISST
  9. , MOB_ADV.MOB_ADV_EXPT
  10. , RD_WORTH.RD_WORTH_ISST
  11. , RD_WORTH.RD_WORTH_EXPT
  12. , VEH_DESCRIPTION.VEHICLE_NO
  13. , VEH_DESCRIPTION.ENGINE_NO
  14. , VEH_DESCRIPTION.CHASIS_NO
  15. , VEH_LIC.VEH_LIC_ISST
  16. , VEH_LIC.VEH_LIC_EXPT
  17. , VEH_TYPE.VEHICLE_TYPE
  18. FROM H_DUTY_PER 
  19. , H_PER, INS_CERT
  20. , MOB_ADV
  21. , RD_WORTH
  22. , VEH_DESCRIPTION
  23. , VEH_LIC, VEH_TYPE
  24. WHERE VEH_DESCRIPTION.VEHICLE_ID = VEH_TYPE.VEHICLE_ID 
  25. AND VEH_DESCRIPTION.VEHICLE_NO = VEH_LIC.VEHICLE_NO 
  26. AND VEH_DESCRIPTION.VEHICLE_NO = H_DUTY_PER.VEHICLE_NO 
  27. AND VEH_DESCRIPTION.VEHICLE_NO = H_PER.VEHICLE_NO 
  28. AND VEH_DESCRIPTION.VEHICLE_NO = INS_CERT.VEHICLE_NO 
  29. AND VEH_DESCRIPTION.VEHICLE_NO = MOB_ADV.VEHICLE_NO 
  30. AND VEH_DESCRIPTION.VEHICLE_NO = RD_WORTH.VEHICLE_NO;
  31.  
And you might like to write your queries this way to make it much easier to debug.
Nov 16 '06 #2

P: 62
Hi. You might add DISTINCT after SELECT
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.   H_DUTY_PER.H_DUTY_PER_ISST
  3. , H_DUTY_PER.H_DUTY_PER_EXPT
  4. , H_PER.H_PER_ISST
  5. , H_PER.H_PER_EXPT
  6. , INS_CERT.INS_CERT_ISST
  7. , INS_CERT.INS_CERT_EXPT
  8. , MOB_ADV.MOB_ADV_ISST
  9. , MOB_ADV.MOB_ADV_EXPT
  10. , RD_WORTH.RD_WORTH_ISST
  11. , RD_WORTH.RD_WORTH_EXPT
  12. , VEH_DESCRIPTION.VEHICLE_NO
  13. , VEH_DESCRIPTION.ENGINE_NO
  14. , VEH_DESCRIPTION.CHASIS_NO
  15. , VEH_LIC.VEH_LIC_ISST
  16. , VEH_LIC.VEH_LIC_EXPT
  17. , VEH_TYPE.VEHICLE_TYPE
  18. FROM H_DUTY_PER 
  19. , H_PER, INS_CERT
  20. , MOB_ADV
  21. , RD_WORTH
  22. , VEH_DESCRIPTION
  23. , VEH_LIC, VEH_TYPE
  24. WHERE VEH_DESCRIPTION.VEHICLE_ID = VEH_TYPE.VEHICLE_ID 
  25. AND VEH_DESCRIPTION.VEHICLE_NO = VEH_LIC.VEHICLE_NO 
  26. AND VEH_DESCRIPTION.VEHICLE_NO = H_DUTY_PER.VEHICLE_NO 
  27. AND VEH_DESCRIPTION.VEHICLE_NO = H_PER.VEHICLE_NO 
  28. AND VEH_DESCRIPTION.VEHICLE_NO = INS_CERT.VEHICLE_NO 
  29. AND VEH_DESCRIPTION.VEHICLE_NO = MOB_ADV.VEHICLE_NO 
  30. AND VEH_DESCRIPTION.VEHICLE_NO = RD_WORTH.VEHICLE_NO;
  31.  
And you might like to write your queries this way to make it much easier to debug.

Thank you so much, it worked like maggic. You seem to realy be a gurry in Programming. Thats great.Have a lovely day.
Nov 16 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.