I'm trying to combine 4 columns. Some of the columns have leading 0's and when I run my query I loose the leading 0's.
BATCH SOURCE NO SEQLINE
11012011 P 004800525
SELECT [BATCH] & "" & [SOURCE] & "" & [NO] & "" & [SEQ] AS RECEIPT, [7ef].LINE
FROM 7ef;
screen shot attached
I am a beginner in SQL, I have a problem regarding combining rows/ columns having value. Below is my example:
Table1:
1 123 23 43
1 234 3 431
2 34 34 34
2 54 3 123
3 1 32 32
2 341 1 32
Output Table:
1 123 23 43 234 3 431
2 34 34 34 54 3 123 341 1 32
3 1 32 32
or instead of combining rows, getting the result by combining columns with the same condition.How can I obtain such result?
I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.
I have an example of what I'm working with (access file) and what result I want (excel file).
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
Hello,
I am trying to combine 2 columns from 2 different tables. Table names are Center and TeachingSite. These tables are stored in an Oracle database, so i can't really change the table structure. I need to combine the Center.Name and TeachingSite.Name field into a list box on a form. This way, everything is listed into one list box instead of a list box for Center.Name and 1 for TeachingSite.Name. Is there a way to create a SQL statement or anything else that would work that will combine these into one listbox and be able to select any item from the list to show related data on subforms and so on. Thanks.
Jared
Hi,
I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?
Thanks
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
Job1......Job2......Job3......Job4......Job5
Joe.......Bob.......Jim.......Fred......Jeff
Joe.......Joe.......Jeff......Jim.......Jeff
Bob.......Bob.......Jeff......Bob.......Jim
and so on and so forth.
I want to have a query that gives one column that looks like this:
Bob
Fred
Jeff
Jim
Joe
I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?
(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)
I have a table in Access with 184,000 rows or records of data and 20 columns or field names. When looking at the table ten of the columns represent the identifying information for the data stored in the other ten columns. I need to convert the last ten columns of data into one column with a second column added to identify the original field name. Then end result would be one current record of data being converted into ten records with the original 10 columns of identifying data, a new column containing the original field name and a column for the data value from the original column identified in the new column.
I have been able to do this in Excel using a consolidation pivot table, un-checking the rows and columns then clicking on the final sum value to get a detailed list of the records. Unfortunately there still appears to be a cap because I get a message saying some of the data has been dropped after a little over a million rows.
I am using the 2010 version of Access and Excel.This is the smaller table. I have several others that have up to 200 columns that need to be converted the same way.
My Approach database contains records relating to nearly 800 sites in London. There are radically different amounts of data held about each site. The database contains a lot of different tables, each containing a different class of information. Not all the sites listed in the database have information in all of the tables.
The unique thing that holds it all together is the unique site reference number. Unfortunately this is split into two separate columns in the Approach database, "Reference" and "Suffix". Not all sites have a suffix. (The purpose of the suffix is to identify sub-sites which are subordinate to the main site reference, but need to have their own individual records.) Because many sites have no suffix, most of the fields in the second column are blank.
In order to link all the tables together in access I need a Primary Key which is unique to each site. In this case the reference/suffix number is the obvious (only) candidate. There is no problem using two different columns to create a primary key. The problem I face is that it doesn't like the fact many of the fields in the second column are blank.
My solution to this is to combine the two columns into one. That would give every site a unique reference, and none of the fields in the combined column would be blank. Can I work out how to do it? All I want is a new column that displays the reference and suffix (if any) in a single field, no spaces.
I'm trying to retrieve information from 3 tables (Order, Receive and Issue) to one table as Inventory Table/Query. it looks like as follows
Order Table:
OrderID
ProductID
OrderDate
OrderQTY
1
1
3/1/2012
30
2
1
4/1/2012
10
3
2
4/1/2012
20
Receive Table:
ReceiveID
ProductID
ReceiveDate
ReceiveQTY
1
1
3/3/2012
30
2
1
4/3/2012
10
3
2
4/2/2012
15
Issue Table:
IssuedID
ProductID
IssuedDate
IssueQTY
1
1
3/5/2012
20
2
1
4/6/2012
10
3
2
4/5/2012
5
I'm looking for Inventory table/query which will give all of the data per date, which is like the following
Inventory:
Date
ProductID
OrderQTY
ReceiveQTY
IssueQTY
3/1/2012
1
30
3/3/2012
1
30
3/5/2012
1
20
4/1/2012
1
10
4/1/2012
2
20
4/2/2012
2
15
4/3/2012
1
10
4/5/2012
2
5
4/6/2012
1
10
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
Current:
ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0
Needed:
ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Access 2007, Windows 7.
I have a query where I am geting the last 4 digits from an id. However my query when it returns the results misses out the leading zero from the results. For instance if I had an id of 12340567 the query should return 0567 but instead it returns 567. This is what I am using:Max(Right([Employee]!
View 3 Replies View RelatedI have an INSERT query like so:
Code:
INSERT INTO table1 (column1, column2, column3, column4, column5)
SELECT (field1, field2, field3, field4, field1 & "" && field2)
FROM mastertable
According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
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] ....
Not sure what happened here.
I now have an empty (277MB) shell in which I cannot see any tables or queries.
The event occurred when the db automatically began to "Compact and Repair" in the middle of creating a new query. Any thoughts?
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
I have two queries:
1. a sum of payments by individuals
2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
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.
hi
i want to comibine some queries into one table
the first quierie contains like 6 records and the second one contains 3 records
the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?
Thank you in advance for your assistance.
I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.
I have a Project table linked to an Invoice Table (1 to many respectively)
The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.
I also have a Reclass table that is linked to the Project table.
Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.
The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.
The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.
I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).
I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).
Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.
I know my explanation is long but I think it will help paint the bigger picture.
Thanks again for reading this!
I have a query called Spans1 that calculates the difference between dates:
SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, [Rasp].Date_Received, [Rasp].Date_Resolved, DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
FROM [Rasp]
WHERE ((([Rasp].Status) Like "*" & [Enter status]));
I have another query called Average_Span that gives me an overall average number of days between the dates in the above query:
SELECT Format(Avg([Span]),"0.0") AS Expr2
FROM Spans1;
Is there a way to combine the 2 so that I get the spans and an overall average? Any help is greatly appreciated.
I have a need to combine two queries into one so I can run it from a function. I have tried everything to no success.
Anyone have any ideas?
Query one [find m owner] this restricts the table to only those records owned by "m":
SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"));
Query two this deletes the latest record added by "m":
DELETE [find m owner].PK
FROM [find m owner]
WHERE ((([find m owner].PK)=(select top 1 P.PK from [find m owner] as P Order By P.PK Desc)));
-------------------------------------------------------------------------------------------------------------------
I tried copying
(SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m")))
everywhere [find m owner]
exists in the second query but received syntax errors
this is the combined query
DELETE (SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))).[PK]
FROM [SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))]. AS [%$##@_Alias]
WHERE ((((SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))).PK)=(select top 1 P.PK from (SELECT Table1.PK, Table1.dog, Table1.owner FROM Table1 WHERE (((Table1.owner)="m"))) as P Order By P.PK Desc)));
Hi
I have a query that uses Count to count the number of Attendances in a register in which there are the letters / Present, A = Absent, E = Excused, L = Late
I have another query that calculates the total possible number of attendances
I am now trying to combine the two so that it will calcuate the Percentage Absent, Percentage Present etc
Can this be done in one query, can the two queries be joined together or do you have to use vba in a Form to
extract the information from the two queries.
thanks for looking
Nick