how to combine tables into a query

Looks like I didn't think far enough ahead when designing my database, now I'm not sure how to combine 2 tables into a query.

Any suggestions would be greatly appreciated.

I have a number of activites that are either the responsibility of a Service Provider (SP), Service Manager(SM) or Departments. Originally I only needed to know which activities were to be done by the departments. Each Department would likely be responible for doing their portion of a given activity. Now I've been requested to relate Dept. A, Dept. B etc to each of the Department activities. I created a Department table no problem (but am not sure it will work- Fields ID NAME DESCRIPTION). My Activity table has the 3 fields SP, SM and Department as either Yes or No.

I can run a query to get all the Department activities. I want to be able to eventually produce a Report that shows Activity 1..completed by Dept A & C ( not by B, D & E)
Activity 2 completed by Dept A, B & E ( not by C & D)etc. Perhaps even include a date when the activity was completed.

My mind is drawing a blank as to how to combine the two tables to get the desired results.

Any ideas???


Post your answer or comment

comments powered by Disqus
Hi there,

I have a big table bringing Access up to almost 2G limit.
I need to add a column to that table using a query.
I can’t make another table because of the volume.

I have 3 columns like

Company Division Branch
W X P

I need to add the column having

Company Division Branch BranchCode
W X P WXP

Thank you.

Hello All,

I'm just beginning to work with Access 2000 and VBA. I've been reading a lot of your threads and have been able to incorporate some of your solutions into my first address book project. Do you remember those early successes? Well, I wanted to do a mail merge using MS Word 2000 and my new address DB. I thought I’d be real smart and create fields for: abFirstName, abLastName and abSpouse in my tblAddressBook table. I then created a query to be selected in Word because I only wanted a few records to be considered.

In my Word document I wanted to have a greeting: Dear and ,

Here is the problem: when a record has no value in the abSpouse field the mail merge goes ahead and prints:

Dear Firstname and ,

Short of “fat fingering” my database to add the prefix “ and “ & spouse name is there a preferred method to get the “ and “ to be omitted when abSpouse field is null? I also don’t want to violate the rules of normalization and create another field, abGreeting, in which the first name and spouse’s names would be repeated.

Thanks in advance.

Divit11

Hi,
I have a contact table and a category table. One contact can be assigned several categories.

I want to be able to have a datasheet in a form show me all vital contact info (name, address, etc) across one row as well as show in one cell of the row a string of however many of the categories this contact has been assigned, separated by commas.

For instance, I'd like the columns to look like this:

	Code:
	Name-------Phone------------Address------------Category
Joe Smith---(555) 555-5555---123 North Street---Volunteer, Civic Leader, Government

Is there a way to write into a query to take the contact ID and for every category assigned to it, string them together (like, above, *Volunteer, Civic Leader, and Government* are 3 categories) into one 'sentence'? Then, I could set the record source for Category in my datasheet to this column in the query.

Hello all,

I hope that the following explination of my problem in clear...

I have a table with a series of dates in individual fields: Day1, Day2, etc.

What I would like to do now is to combine those into another query/table where the dates are all in one field.

ie:
Original Table
[Day1] March 1
[Day2] March 2
[Day3] March 3

New Table
[Date] March 1, March 2, March 3.

Can you help me figure out how to accomplish this? I greatly appreciate your time and sharing of any thoughts that you have on this situation.

t

Hello,

I'm trying to build a string as expression into a query. What i want to do is to get the field [Address],[PostalCode],[Village],[District] from table tblApplicant and build an address block as the following but i don't know which character to use for changing line

Address Block
-------------------
[Address]
[PostalCode] [Village]
[District]

Any suggestions?

Thank you in advance!

Hi all,

Hve a table with the following fields:

ID
ADRESS
#APARTMENTS

Need to do a mailing that prints for the same ADRESS as many copies as #APARTMENTS

I was thinking in making a temporary table from a query which duplicates each as #APARTMENTS but don't know how to do it.

What is the best way to do it?

Tks in adv
Paulo

I've produced a form which is linked to a number of tables via a query. My problems is that I'm about to run out of fields in the supporting query.

How can I make all of the fields in the related tables be available to the main form?

I have a list of things, where each thing can be classified into 3 different classicifications. I want to count how many classifications of each thing there are in my list as well as see what percentage these classifications make up in my list. Only issue is, I have no idea how to work this into a query. I can get a numerical list just fine, but when it comes to percentages, it seems like rocket science.

Let's say I have a table like this.

Entry, Classification
1, A
2, A
3, B
4, C
5, B
6, B
7, A
8, B

So there are 3 A's, 4 B's, and 1 C. I want to produce a query that counts how many there are, and what percentage they make up. I want to make a query that looks like this:

A....3.....37.5%
B....4......50.0%
C....1......12.5%

Should be pretty simple, no? Yet I can't figure it out. I always get errors. For one thing, I don't even know how to use the "CountOf [Field]" in differnet fields.

I'm sorry but I'm a very honest straightforward person & if I seem angry it's ONLY at myself. While working in one of my (many) databases I realize that I could cut down oin input data by relating 2-tables in a query, but of course it's NOT working for me naturally. The database is all of the NHL Games in a given season -- almost 3,000 Records. Each time a team plays I enter the 'Date' Home or Away' 'Conf' 'Div' 'Team' etc in a Table named, Results. I also have a table named, Teams which contains varies info regarding each 32-teams including the 'Conf' & 'Div" 'Team." Just today it had occurred to me, why can't I pull the 'Conf' & the 'Div' from the Teams Table into a query with my Results.?

Eg. The team Philadelphia Flyers (PHI) in in the 'EASTERN' Conference & in the 'Atlantic' Division. which I have in my Teams Table just once, but many times in my Results Table. I tried joining the tables a few ways, but nothing is working.

- I tried joining the TeamsID from the Teams Table (1) to the TeamsID (many) in the Results Table. When I ran a query pulling all of the fields from the Results Table except the 'Conf' & the 'Div' fields which I pulled from the Teams Table -- no results. Now, out of trial within this query Ijoined the 2-'Teams' field from both tables & I got my results. However one problem, it won't let me edit or add new records.
- I also tried relating the tables by the ResultsID...?

I really hete myself for continually pestering you guy, but as I always say I reallyapprechiate you guys

I am trying to repair the (ugly) data I have had to import to my database. I am working with Access 2000. The problem is this: For many of the records, the data I am receiving (and have no control over) is split into multiple entries because one of the fields was too large to fit into one record at the source because of size limitations on one of the fields (Info ).

The table looks like this:

RecordID (this is the autonumber primary key generated by access)
Lastname
Firstname
StudentID (unique six-digit identifier for each student)
LineNum (the order of the Info entry for this student)
Info

The Info field is the troublemaker. Most students have one or two entries to accommodate the length of the Info data for that student. Average is about three lines. Max is 22 lines! So most students have multiple records which I simply need to combine into one record. They need to be combined in the right order (by LineNum ) so that the Info data will look right.

I am not sure how to set up a query (or if there is a better method) to accomplish this.

Hello,
I have around 40 tables right now on hand and I would like to combine them into one big table (a table, not table formed by query) and I am wondering is there a easy way of getting it done without me physically copying and pasting all 40 tables? Thanks!

regards,
Anyi

Hi guys how can I combine records from different tables into one query?

For example I need a query for a form that shows client's balance history, where Invoices/Bills and Payments are coming from two different tables.
Here is an example how this might look on the report:

1/1/2012 Invoice $500
1/8/2012 Payment $250
2/1/2012 Invoice $500
2/8/2012 Payment $750

Like I say Invoices would come from one table and Payments from another, and because it is relevant to another project of my lets assume that I cannot combine the two tables into one.

Thanks

How to combine two tables in one table such that values are neither duplicated nor missed?

please, see the attached screen shot that show my question


i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!

How can i perform this kind of combination?

thank you in advance,

regards

Jamal Attached Thumbnails   Attached Files M4.zip (66.0 KB, 0 views) Reply With Quote 10-28-2011, 06:39 AM #2 Robeen VIP Windows XP Access 2010 32bit Join Date Mar 2011 Location Tulsa, Oklahoma. Posts 1,500 There may be a simpler way, but try this:

Create a new query
1. Select Field1 from Table5.
2. Got to View -> SQL View.
You should see something like:
Code: Select Field1 FROM Table5; 3. Change the SQL in the same SQL pane so it looks like this:
Code: Select [Table5].[Field1] FROM Table5 UNION Select [Table6].[Field1] FROM Table6 ORDER BY [Table5].[Field1]; If you run this - you will get all the field1 values - no duplicates.
4. Save the Query and name it, for example, Query1.
5. Create a new Query.
6. Select - in this order: Query1, Table5 & Table6.
7. Join Field1 of Query1 to Field1 of Table5 & Field1 of Table6.
8. Right-click the Join lines and make the Join Properties of both option 2.
You should see an arrow pointing towards Table5 & Table6 on the join lines [in your new Query].
10. Select Field1 from Query1, Field2 from Table5 and Field3 from Table6.
11. Run the Query to verify that you get the results you need.
12. If you really need it to be a new table - convert Query1 into a MakeTable Query and run it to create your new table.

There may be a more sophisticated [a single query] way of creating the resultset [crosstab query, perhaps??] but I do not know off hand how to do that.

I hope this helps.

I originally posted this in the incorrect forum so thought I should move this to the report forum.

I have a query that gives me data which includes dates. The data is captured for a week long period and subdivided by a project name. The result I would like to have in the end is a report which vertically lists a project name and then horizontally lists Monday, Tuesday, Wednesday, Thursday, Friday and Saturday. I would like the report to state that "X" amount of hours were applied to project "1" on Monday, and "Y" amount of hours on Tuesday etc etc.

My query is created to provide that data but I am unsure of how to get that into a readable table format on a report form, preferably in a grid format if possible with weekdays horizontally and projects vertically.

Thank you for any assistance in advance.

I have a table which contain data as below:
ProductCode/Amount_dollar
10/200
10/300
10/20
10/50
20/100
20/100
30/300
30/450
30/200
50/600
50/250
60/160
60/230
70/360
70/250
70/420
Total/3990

Where 10 & 20=cake
30 & 50=biscuit
60 & 70=muffin

I would like the output to be like in table:

Product/Subtotal
Cake/770
biscuit/1800
muffin/1420

I have tried this :

SELECT count([ProductCode]) AS count, Sum([Amount_dollar]) AS Sub_Total
FROM table WHERE ProductCode In (10,20);

Then I have to repeat the same query for 30 and 50, 60 and 70 respectively. It is quite tedious.
I truly have no idea of how to combine it all in 1 query.

Much appreciated If any one have good suggestion of query or methods in Access to help me get the output. thanks

I am truly baffled! Can anyone help me figure out how to combine the contents of several fields into another separate field or if there is a better way to achieve my goal that would be appreciated also.

I am developing an application to keep track of projects. The users can pull up a form, called frmDatabases, and check boxes to indicate what databases a client has access to (these are researchers at a university). They can check from 1 to 10 possible databases. Of course, when a box is checked, a "-1" is placed in the field in the tblPROJECTS table

Then I need to be able to include a field on a report that lists the names of the databases checked for each record and separate these with a comma. For instance, the label on the first checkbox may be called "ASD" and the second box "CIHI" and those are the terms I need to have appear in the list on the report.

Hope this is not too confusing. Any ideas?

Hi all,

Wondering if anyone can help here? I am currently working on a form that needs to display data from 3 unique queries - a crosstab query and two standard queries (one has line by line data and another summary data) that do not have a common link.

What is the best way to do this? Would it be possible to display the results of each of these queries in a single form by creating 2 subforms from 2 of the queries and placing them in the form of the 3rd?

Any suggestions on how to combine the data from the 3 queries into the 1 form would be much appreciated!!!

Thanks in advance.

G.

I have 5 text boxes that are all calculated in vb. They contain counts of record sources or totals of a subform sum or derivations of those. I want to create a chart based on the values in the text boxes, but I can't figure out how to get the data into a query or table to serve as the row source for the chart. Basically, I just want the values in three different text boxes to create a simple pie chart. What am I missing?

Do I need to create a one row table that will serve as the source for my chart and keep it around or is there a way to create a temporary table? Is there a table datatype in vb that I can use to make a table variable (like a recordset)? How would I populate it? I have a bunch of these little charts to create, so I don't really want a ton of one row tables sitting around.

I'm new to access and I feel that I am missing something really obvious so please forgive my ignorance.

Kman

Hello all,
I'm a very new Access user, and this is my first database project. It's supposedly a simple database designed to track the checking in and out of Aircards (cellular cards for laptops). I'm not very good at explaining what I want to do, and I've searched for this solution, but I haven't come up with anything that clicks in my brain - but here goes. =)

1. On my main form (Add Aircard Form) and table (aircard_master) there is a value for Current Assigned User (aircard_master.userid)
2. On the main form, there is also a subform that shows actions associated with the currently selected aircard on the main form. (actions such as when it was issued, returned, activated, assigned a phone number, etc) and each action is required to have a date assigned to it. These actions are stored in the table (aircard_actions)

My proposed problem is that I want the Current assigned user to be auto-populated with the last user (aircard_actions.user) when data is entered into the subform (aircard_actions). All this can be seen/done on the main form. At this point, I'm unsure whether it needs to be done in a query, as code when a new action is recorded in that table, or what. But, the data needs to update the original table (aircard_master.userid) automatically based on the latest DATED entry for that associated card (aircard_master.inventory_number), from a non-null entry in the (aircard_actions.user) field.

This was confusing to me even as I wrote it, I know the people here are a lot smarter than I am on these matters, so maybe in makes sense to you. I'm going to try to find a way to link an attachment of the database with test data in it if anyone wants to take a look. It's 2.5MB so I can't attatch it here...
Oops, just figured out how to share it online. Here's the download link for it:
http://temp-share.com/show/KdPfsL3ih

Thanks in advance if anyone has any ideas!
Mike

Hello all, thanks for looking at my question. I am really quite new to Access (as you will probably tell).

I have created a table with a Make Table query which used a UNION query as the inputs.

The Table created has no primary key set and I wanted to set the primary key as the field 'Product' as this matches with another feld in a different table which I will use later. I would prefer it if this could be done as part of a query as I will carrying out this everyday on many tables and intend to make it into a Macro.

If anyone can tell me how to do this I would be greatly appreciative.

Thanks

Working in Access 2007.

I am working with an existing database that has members in a table with up to 3 e-mail addresses for each - these are in separate fields e-mail1, e-mail2 and e-mail3.

I need a list of all e-mail addresses (that is a list of e-mail1, e-mail2 and e-mail3 combined into a single column). It does not need to contain any other information - its just to create a simple text file that I can upload to a listserv with everyone's e-mail address.

I can't for the life of me figure out how to combine several fields into a single column list.

Right now, I'm able to create a query of all e-mail addresses, but not surprisingly, it creates a three column list. I take these and put them into excel where I manually combine them into a single column and delete any blank cells. I want to automate the entire process so that I have a single text file with a list of e-mails.

Hope this makes sense to someone.

Hi everyone,

I have an access database that basically checks supplies in use and checks them out after they have expired. I have a table called All Supplies that holds the item's name, the date it started being used, its expiration date, and the date it actually stopped being used and how many times it was used. I have a query called NowInUse based on All Supplies that shows only the supplies that are still being used. I did this by setting the criteria in the query for the field Date Use Stopped to is Null. My problem is that I need this list of supplies in the NowInUse query to be inserted into a ODBC sql table called dbo_InUse that is already in the database. I've tried using an append query but I get the message:

ODBC--insert on a linked table 'dbo_InUse' failed.
[Microsoft[ODBC SQL Server Driver][SQL Server] The INSERT permission was denied on the object 'InUse', Database 'SupplyDatabase',schema 'dbo'.(#229)
The database tables and the sql table are not set to read only as far as I can tell , all of them have primary keys, and when I linked the table into the database I provided the password. I'm not sure what is going wrong. Can anyone help? I'd really appreciate it.

Thanks!!

Hello there! This might not be the right section to post in but it's regarding tables. Basically I have some SQL statements that copy data to a new table, which is written in VBcode and operates on the click of a button.

Basically the only problem is the messages:
"You are about to paste # rows into a new table/table"
"The existing table will be deleted before you run the query"

The kind of message depends on the way I organise the SQL (either using it to append data (as in INSERT INTO tablename SELECT blah blah) or overwrite (SELECT blah blah INTO tablename).

I'd rather use the latter because it facilitates the desired wiping of all previous data and replacing it with fresh records. However I don't want to have to click "yes" every time this is done? Is there some VBcode perhaps, like the On Err code that I could use to get access to not pop up these messages?

I have two table with one same/similar field.

Eg:
table 1
WorkUnit Student
1 julie
2 jack
3 peter


table 2
WorkUnit Teacher
1 Jean
3 Dan
4 Chris

I want to combine table 1 and table 2 as table 3
so table 3 will be
WorkUnit Student Teacher
1 julie Jean
2 jack
3 peter Dan
4 Chris

I try to use append query, but it seems that I can not have duplicates for workunit. Thanks a lot for the help


Not finding an answer? Try a Google search.