Hi All,
I have a Append query which has columns from two tables. Now i need to create a new column "Sum" in new table which should Add two of the Number columns .
That is : I have column A and Column B; I need to create a Column C which contains (A+B)
Please advise.
Thanks,
Kon
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
Hi,
I'm new at access but getting the swing of it. It is a pretty cool toy.
I have a table that I am adding various columns of data. Once added I move the columns to where I want them to be. I am hitting the little save disk on the top toolbar but when I reopen the file the columns are not where I placed them. I'm not sure why this is happening. Any thoughts?
Thanks in advance for your help.
Eddie.
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar
Hi I have search the forums for an answer to this..i have 4 fields address 1, 2,3,4 and i would just like to all all the information to one field call address..how can i merge the information..please bear in mind i am useless at access..i thought there might be a way of copying the columns and just having a merge function to do so ..but cant find anything...please help..Williebear:(
View 4 Replies View RelatedHello again!
How do I prevent the information in lookup columns from showing up in other tables, queries, etc... as numbers.
Example.
Lookup to Fruits. Where the records for fruits are Apple, Banana, Orange, and Pear.
The lookup column lists all the items {Apple, Banana, Orange...}, but in queries the data just appears as numbers {1, 2, 3}.
Many thanks for anyone's help in advance!
Hello.
Here's some background on what I'm trying to do. I am building a database that will hold data on 31 parks. Each park will contain many advertisers and each advertiser may be in one or many parks. Theres more to this but i will just stick to what I am having trouble with.
The tables i have made thus far pertaining to my question are:
1. "Parks" table. It has two cols one for ParkID (key) and ParkName
2. "Advertisers" table. It has 9 cols for things such as AdvertiserID (key), addresss, etc...
3. "AdsData" table. This is the problem table. It contains AdID (key) and ad content columns (copy, names, driving directions, etc...). In addition to the ad content however, I need each ad to indicate which of the 31 parks it is located in (one or many) and which of the 16 park activities (things like hiking, swimming...i have another table for these) it is to appear in (one or many).
The only solution I have been able to come up with is to make a table ("AdsData") with 55 columns in it. In addition to the ad content data for each ad, there are 16 yes/no columns for selecting one or more activities for the ad to appear in and 31 yes/no columns for selecting one or more parks for the ad to appear in.
This seems excessive because I have a table with all the Parks in it and I have a table with all the Activities in it. Plus I've been told that having so many columns in a table is a bad, bad thing.
But in my limited knowledge of Access, this is the only solution I can come up with.
Is this the only way to do this?
I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:
Budget Total Expenditures Balance
Thanks.
Hello,
I cannot find anyway to do a querry search in different columns.
For example:
a movie database, a movie has more genres, so i have a column for genre1, genre2 and genre3. now i want to be able to search in all three columns at the same time for 1 genre, is that possible ?
If I have a date column and another column with another consistant date, Can I make a criteria to compare one to the other. Ex:
END_DATE Today's_DATE
06 Feb 05 15 Jun 06
12 Feb 05 15 Jun 06
01 Jan 06 15 Jun 06
26 Mar 06 15 Jun 06
18 Aug 06 15 Jun 06
28 Dec 06 15 Jun 06
Can I make a criteria that will only show the END_DATE data that is before Today's DATE? Does it need to be a new column or can I place it in the criteria of one of these column.
Hi
I have a confusing situation. I have a need of creating a combobox which displays records from different tables.
Eg.
I have a table called "house parts" and filled with records like
room, hall, garage
Secondly i have a table called "Parts" and filled with
floor, ceiling, lamp, window etc.
As u see, "house parts" could consist of "Parts" like "room" could have floor, ceiling etc.
I need to create a query that shows me all the records from "house parts" and also "Parts" in the same list.
Can anyone help me please
NEVER MIND--- I FIGURED IT OUT:o
I have a table with two columns.
FIRST LAST
James Jones
R Kevin Foster
When I use this:
Left(FIRST,1) & " " & LAST AS Expr1
I get: James Jones
R Foster
Is there a way to correct this so R Foster comes back as R Kevin Foster?
Hi all,
Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc
transform it to
701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....
Thanks in advance
I have a column in a table that contains the date and the time, i want to be able to split this column into 2 so i have one column for date and one for time
msgtime
03/07/2007 09:59:00
03/07/2007 10:02:00
03/07/2007 10:02:00
03/07/2007 10:04:00
03/07/2007 10:04:00
03/07/2007 10:39:00
03/07/2007 10:39:00
above is the column is a sample from the column i need to be split.
Any help is very appreciated
Hi,
I'm a newbie to Access. I have created a query showing the result base on other calculation. The returned results are in numeric. I would like to know how to sum up the returned results on row to a new column.
A b new column
100 200 300
200 150 350
Your kind help will be highly appreciated
I got a access SQL question.
Let's say we have two int columns, I want to get the results Column1 + Column2 in string format
i.e
Column1 - Column2
5 - 2
6 - 2.5
10 - 6.5
10.5 - 8
I need to return the data as new column like:
NewColumn
"5 to 2"
"6 to 2.5"
so on
SQL + operator only works for string operation, how would I use it for numeric data type.
Thanks a bunch
shazaduh
Folks,
I need your help on GROUP BY clause. I have been struggling to get his report in a way I wanted for some time now. Here's what I like to do:
I am writing some project related data into a temp table (tblAppendForRskTblReport) by clicking a command button on a form. The data in tblAppendForRskTblReport looks as shown in the excel file tblFile1.xls (attached).
I wanted to make a report in Access 2003 which looks like another excel file rptFile2.xls(attached). As noticed, I wanted group the records into two main groups based on column "fCurrentlyOffered". Then for one group which reads "Yes" (column "fCurrentlyOffered") divided into several sub-groups based on column "chrCategoryID1" like Category A, category B, ....
I tried to use the following query:
SELECT A.chrProjectName, A.chrAqcsPropNum, A.memGuranItem, A.memFuel, A.memOpertgCondn, A.memPred, A.memGuar, A.memMargin, A.memCustReqst, A.memPermitValues, A.memRiskLevel, A.memLDs, A.memBasis, A.memCorrectAction, A.memComments, A.intCategoryRowID, A.chrCategoryID2, A.fCurrentlyOffered, Count(*) AS RecordCount
FROM tblAppendForRskTblReport AS A
GROUP BY A.chrCategoryID1, A.fCurrentlyOffered;
I could get a report based on column "chrCategoryID1" only (see attached sample excel file rptFile3.xls), but don’t know how to separate the rows (as new group called ‘Not Offered’) which reads 'No' under the column "fCurrentlyOffered".
Any attempt I make by modifying the above SQL leads to an error msg as follows:
"You tried to execute a query that does not include the specified expression 'chrProjectName' as part of an aggregate funtion."
I have Count(*) as an aggregate funtion in my SQL but I don't know why it's still complaining?
My question is: can I get the report as I mentioned above (as shown on rptFile2.exls) using SQL or do I need to handle this via VBA?
Can someone help me on this or offer any tips/idea?
PS: Some of the column data may looks like redundant info (normalization), but they are purposely replaced with 'deleted' or 'N/A' words for privacy.
Thanks/Shan.
Hi
Can someone help me with this query below.
Is there a way to list all columns in a table that contain Null Values. I have imported data into a new table but not all columns contain data. I need to write a query to list all the columns that have null values.
The result needs to give me a list of columns that do not have values in it. These need to be across the entire column ie if there is data in 1 row out of 1000 rows in column A, then the query should exclude this as it contains data.
I hope this is not too confusing but i am having trouble getting this query to work.
Avinash
In my table i have three columns for data entry. They are Subject 1, Subject 2, Subject 3. I need to be able to produce and query that will bring results up if for example 'english' is in anyone of these columns. I know how to do it for just one but i need it to be able to check all three columns?
Hope this makes sense
Sam
So the database this user was using was made in Office 97. The query to sum two different columns in two different queries worked fine. Then we upgraded her to Office 2003 and converted the database and it no longer worked.
Here is what I need to do:
In total query there is a sum of account and sum of extended column that sums everything in the account and extended columns from table1.
In total a query there is a sum of account and sum of extended column that sums everything in the account and extended columns from table2.
In my grand total query I want two figures returned...one will be the sum of account columns from both total and total a queries. And the other figure will be the sum of extended columns from both total and total a queries.
I have tried this numerous different ways and always get the same result...a blank returned in the grand total query. From some research I've done this could be because one of the tables/queries frequently returns no data?
Thanks for your help!
Hi!
Say I have a table/qry:
ID | VARIETY | PRODUCT
1 | Fresh White | Garlic
2 | Fres Valencianita | Onion
Is therey a way to merge variety and product? Cause I want to make an invoice description with a text that can be made out merging fields of a same table/qry.
Sth like the following:
"[QUANTITY] of [VARIETY] [PRODUCT] from [COUNTRY], size: [SIZE]...."
I saw a topic about listing items horizontally but I think it was kinda different from what I wanted, specially cause involved more than one table.
Hi,
I want to have an index such that the combination of "line no" and "calving date" are unique.
I have tried:
CREATE UNIQUE INDEX index1
ON [main table] ([Line no], [Calving Date])
;
And
CREATE INDEX index1
ON [main table] ([Line no], [Calving Date])
WITH PRIMARY;
but I get a unique index on Line no
Any suggestions?
Regards
lactaman