473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to Combine Entries from 2 Separate Tables

I have 2 tables: one has a list of category names (ex: 1,2,3,4,5), and the other contains a list of items(ex:A,B,C,D,E).
As an output, I want to get a list of entries that are combination of category names (numbers) and items (alphabet) taking the first entry of category (1) and connecting to the first item of item (A) to get a new entry of 1A. Then LOOP it until you get 1E. Then 2A to 2E, then 3A to 3E and so on until 5E.
the output list should look like:
1A
1B
1C
1D
1E
2A
2B
~omit
5C
5D

I am just starting to learn Access VBA and strugling.
Can anyone help, please?
Oct 21 '11 #1

✓ answered by Stewart Ross

This can be done with no VBA at all simply by taking the Cartesian product of the two tables. The Cartesian product is where there is no join between the two tables, with each row of table B repeated for every row of table A.

You have not told us the table and field names, so you will have to replace the names mentioned below with the ones applicable to you. The SQL is simply

Expand|Select|Wrap|Line Numbers
  1. SELECT A.[Your Category Name] & B.[Your Item Name] FROM
  2. [TableA] as A, [TableB] as B;
Athough this can be done in VBA there is no need to do so, and (other than for a student assignment) I can think of no good reason to go down this route at all. Please note that we cannot provide full-code solutions for student assignments, as this would not be fair, so I do not propose to say any more about a VBA solution to this question.

-Stewart

5 1799
Stewart Ross
2,545 Expert Mod 2GB
This can be done with no VBA at all simply by taking the Cartesian product of the two tables. The Cartesian product is where there is no join between the two tables, with each row of table B repeated for every row of table A.

You have not told us the table and field names, so you will have to replace the names mentioned below with the ones applicable to you. The SQL is simply

Expand|Select|Wrap|Line Numbers
  1. SELECT A.[Your Category Name] & B.[Your Item Name] FROM
  2. [TableA] as A, [TableB] as B;
Athough this can be done in VBA there is no need to do so, and (other than for a student assignment) I can think of no good reason to go down this route at all. Please note that we cannot provide full-code solutions for student assignments, as this would not be fair, so I do not propose to say any more about a VBA solution to this question.

-Stewart
Oct 21 '11 #2
NeoPa
32,556 Expert Mod 16PB
Following on from Stewart is always a thankless task (as he covers everything so thoroughly straight off) but I will just draw your attention to SQL JOINs in case that can help with your understanding of the matter.
Oct 21 '11 #3
Stewart & NeoPa: Thank you for your reply.
I am not a student and this is not a school assignment.
I am administrative assistant and creating an access database to manage our data better. So Im not even a beginner in this field and I do not exactly know the difference between VBA and SQL to start off... :(
I have taken some courses in Access VBA, and that's pretty much it on the topic.
Is this something simply done using some formulas in query?
In where can I use the code Mr.Ross wrote above?
Oct 24 '11 #4
It worked!!!!!
Mr. Ross - Thank you so much for your help!
Oct 24 '11 #5
NeoPa
32,556 Expert Mod 16PB
It looks like you may have a bit of a learning curve ahead of you Noah, but it seems also you've made sense of this to get it working so a good early step.

It seems likely you'll be back with more questions to help you along, which we welcome. Let me suggest that reading up a little (There are helpful sticky threads in this forum) will save you a lot of wasted time and effort. Best of luck in your endeavours :-)
Oct 24 '11 #6

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

Similar topics

4
by: jeff brubaker | last post by:
Hello, Currently we have a database, and it is our desire for it to be able to store millions of records. The data in the table can be divided up by client, and it stores nothing but about 7...
1
by: Larry Rekow | last post by:
I have a report that's created each day as a flat textfile. Because I came from the Access world, I created a macro that imports it with a schema that gives meaningful names to the various...
3
by: Scotty | last post by:
I have a database which is made up of several tables "contacts", "coaching", "clubs" etc. The Contact and coach tables are linked by an "ID" field. The Contact and clubs table are linked a by a...
4
by: Markus Ernst | last post by:
Hi Looking for a possibility to get separate tables rendered with common column widths, I was surprised to see that this code validates: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"...
7
by: alexander324 | last post by:
I have an access table that has around 4000 records showing various carrier names, origin and destination city, state and zip along distance and cost etc. Is there a way to automate a process that...
2
by: stevenkwlee | last post by:
Hi I'm new to Access (2003) and i've just been given the horrible horrible task of trying to add extra functionality to an exisiting access database. I'm usually alright in the database front,...
6
by: srathi | last post by:
hi, how do i compare two varchar fields? my table has two fields, name and description. i need to find those records where the name does not match with or exist in the description field...
0
by: Pasquale | last post by:
I have a form with 8 fields, 4 of which are required and 4 are not. Usually if a field isn't required I would create a separate table for it rather than having a blank value in the table where...
3
by: praveena mani | last post by:
What are the advantages of storing addresses of students or customers in a separate table in database?
1
by: krizzie khonnie | last post by:
Hi, Can anyone help me? What query should I use if my objective is to check if a record exists in either of the two separate tables.They have the same column name. Ex. Column Name -...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.