Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.
I am using Access 2010.
How can I add the resulting records from a union query. The results of the union are something like this:
Quantity Item
2 Cats
3 Cats
1 Dog
4 Mice
What I need to display is
Quantity Item
5 Cats
1 Dog
4 Mice
How can I get the query to "do the math"?
Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.
Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.
If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.
I have two basic lists of employee names, ID's, phone numbers etc, with one field containing an attachment with a picture of their ID. I don't want to permanently combine these two lists of employees, as they work in different departments, but for certain events I need to be able to print a report with a list of all their names, ID numbers, and corresponding ID photos.
I tried to make a UNION query, but can't do it with the attachment field. If I leave that field out, it's no problem. To simplify, I have been doing a test run as follows, with just the name field and photo field (field 1).
SELECT [Starting Gate employees].[Employee name], [Starting Gate employees].Field2
FROM [Starting Gate employees]
UNION
SELECT [Farrier employees].[Employee name], [Farrier employees].Field2
FROM [Farrier employees];
Hi
I have several small tables that I want to make combine into a big table.
How do I do this - is the only way by using a query?
Maria
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.
Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000
[code]...
can't seem to be able to do so..
View 2 Replies View RelatedI searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...
When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".
I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?
Perhaps I should insist that our IT department upgrade. :p
Thanks in advance for any assistance.
- Matt
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
I have a table with a field I want to update with the value of a Union Query
The field I want to update is Yes/No format and I can update using 0 or -1 OK.
However, when I link in the union query (and check that I only have 1 update result which is either 0 or -1) I get an error message saying that Operation must use an updatable query.
I guess this is referring to the Union Query (although I am trying to update the table and not the query.
In short - I want to update a table based on the value of a union query.
Code:
UPDATE tbl_StaMe_NGR_Subscription INNER JOIN qry_QDF_QRYDEF1 ON (tbl_StaMe_NGR_Subscription.EmailType = qry_QDF_QRYDEF1.Type) AND (tbl_StaMe_NGR_Subscription.AgentEmail = qry_QDF_QRYDEF1.Email)
Code:
SET tbl_StaMe_NGR_Subscription.Subscribed = [Subscription];
I am using below Union Query to unpivot attached table. Basically this is a table with some clothes and their sizes (in yellow). Now my problem is that below query works well only when all sizes are in the same row but as per attachment I may have them potentially over three different rows.
I can determine which row is for which item based on size range column (in red) but I still do not know how to modify my query to look at different rows depending on value of size range column, especially that as you can see there are multiple values pointing out to the same size range i.e. 0-6 and 1-4 point out to Size Range 1.
Unfortunately I cannot change the layout of the table to have all sizes in one row
Code:
Select Field1, Field2, "0" as Size,Field3 as Qty from orderform where Field3 > 0;
Union All Select Field1, Field2, "1" as Size, Field4 as Qty from orderform where Field4 > 0;
Union All Select Field1, Field2, "2" as Size, Field5 as Qty from orderform where Field5 > 0;
Union All Select Field1, Field2, "3" as Size, Field6 as Qty from orderform where Field6 > 0;
Union All Select Field1, Field2, "4" as Size, Field7 as Qty from orderform where Field7 > 0;
Union All Select Field1, Field2, "5" as Size, Field8 as Qty from orderform where Field8 > 0;
Union All Select Field1, Field2, "6" as Size, Field9 as Qty from orderform where Field9 > 0;
I have an existing database being used for for order processing (normalized, working). The order table houses the general order info and a separate detail table holds 1 or more orderdetail records for each order in the order table, related by orderID.
A user has asked that I find a way to import her data into the system from an excel spreadsheet. She has individual columns for each type of order detail record, so for each "order" row, there may be 1 or more columns of "orderdetail"s that I will need to parse into the correct tables.
I imported the raw excel into a table, but I need to append that data into the order and orderdetail table rows (i.e. I have to create the order and orderdetail records that match every other record in the system).
It looks like I need to somehow perform a looped INSERT INTO [ORDER] (field1, field2....) VALUE (val1, val2) but there are those related detail records to contend with (the orderdetail table entries) which may be more than one insert....
Test set of data is attached.
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
View 2 Replies View RelatedHow do you establish the Mode. I can't run a Make Table because the Mode is Disabled and blocking the action
View 1 Replies View Relatedcounting in access on a make table query.I work for a recruitment company, and have a table for the vacancies we raise. I then have a table linking to this where you can record the candidates submitted for each role. The 'CVSubmitted' and '1stInterview' are a check box yes/no. I now want to run a query where I have a sum of the total number of CV's and 1st Interviews for each role.
View 1 Replies View Relatedi am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields
GLcodes
Description
and frmTransaction contain
Glcode
transaction type
debit
credit
date
narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?
View 7 Replies View RelatedHow can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies View RelatedI know that this could open a can of worms but is the above statement true. I have always avoided using delet and append due to database bloat. I know its more difficult to set the field types in a make table query but it means that the database doesn’t need to be compacted nearly as much as delet and append.
If my understanding is incorrect please explain top me why delete and append are better.
Thanks
Hello All, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web..? Or should I just do all the SQL joining and linking stuff just with more SQL code?
Just curious about the impact Make-Table queries have on the Web.. Slower? Not a good idea?
Thanks for any input you can give
John
I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
View 6 Replies View RelatedI have a linked table with 3 significant columns in it:
Marque
Model
Volumes
(there's actually about 12, but I only need these)
and I need to create a new table summarising the contents and creating new columns at the same time
Marque
Model
Model_name (concatenate marque and model, easy to do)
Vols sorted: Descending
Rank (this is the problem)
I need to 'Rank' the table so the model with the highest volumes is ranked #1 the second highest is #2 and so on.
Is there a command within access to allow this to happen? in SQL-Plus from Oracle I can use the Rownum command to create the entry, but this does not have an equivilent in access.
At present, I'm creating the table without the rank field, then adding it in design view, setting it for autonumber, saving, and resetting it to number. This is long-winded and frankly, messy and wrong. I shuld be able to do this in one go, but I can't. I've been using access for about 6 years solid and have not been able to resolve this 3-month-old problem and it's driving me mad.
Thanks in advance for your help.
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 3 Replies View RelatedI have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 2 Replies View RelatedI linked table rawdata from a database called competitor from a ODBC Database. I run a query with to make a table with a criteria where it retrieves roughly 10 columns with 719,000 rows. And it gives me this error.
(Cannot open database". It may not be a database that your application recognizes, or the file may be corrupt.
I asked the creator of the database and they said the database grows automatically and it was created with the same Access version as i am using to run the query.
Also when I do a RUN without creating a table and just selecting and displaying the data it does not give me any issues.