Combining Date Columns From Different Tables And Retrieve Data
Aug 17, 2012
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
View Replies
ADVERTISEMENT
Aug 16, 2013
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.
View 2 Replies
View Related
Jul 17, 2013
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.
View 14 Replies
View Related
Mar 29, 2012
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.
View 5 Replies
View Related
Jul 5, 2005
As the title suggests I would like to combine the data from two tables.
The first table records customer orders in fields - Part No / Quantity / Reqd Date Of Delivery
The second table is an output from a scanning s/w package we recently installed.
This provides the following data - Pack Qty / Part Number / Date Scanned.
The first thing I did was create a simply summing query to add up all the pack qtys for a specific part on a specific day.
Now comes the hard part.
What I want to do is sort and combine both tables in chronological order.
Where the part number & date for an order and a delivery match, I require the record to be combined to one record (in this query).
Where it does not match, then it should remain as two seperate records.
So for example if you ordered 100 footballs for delivery on 11/11, and we delivered 110 footballs on 11/11, the query would return:
Footballs 11/11 100 110
If the delivery was early / late we would get:
Footballs 11/11 100 0
Footballs 12/11 0 110
Now this last bit is where my queries (simple cross table queries) falls down as I only have three options when creating the join properties, ie both match, all from left table or all from right table, whereas I want all from both tables even if they do not match.
Maybe a query is the wrong way to go, but as the table from the scanner s/w is updated on a daily basis it would seem to me to be the best way forward.
Can anyone help?
View 2 Replies
View Related
Feb 7, 2006
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
View 8 Replies
View Related
Jan 11, 2008
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
View 1 Replies
View Related
Sep 4, 2007
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!)
View 3 Replies
View Related
Jun 21, 2012
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.
View 6 Replies
View Related
May 20, 2013
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?
View 3 Replies
View Related
Apr 18, 2013
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
View 6 Replies
View Related
Sep 29, 2014
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).
View 2 Replies
View Related
Sep 14, 2013
I have a project concerning lists of external files. The spread sheet holds the source data on a document per line basis. Each row contains vital data ClientID, ClientName, DocDate, DocDescription, MainPage, Page1, Page2, Page3.... up to Page 585
I have done previous work where importing up to 50 columns has never been an issue.
For some reason in this case I am unable to import more than up to 255 columns and also I lost all the reference data past column 30.
I have tried importing directly to a SQL Server Db - same issue
Access 2010 and this in 2013
View 3 Replies
View Related
Feb 4, 2015
I have various columns of data, a mixture of text and numbers, which all have data in them, and a few columns that have numbers or text. When I copy'n'paste-append into the Access table, the text in the sparsely populated columns doesn't copy across but the numbers do.
It works if I import the spreadsheet from scratch (don't want to do that however, that data comes from somewhere else and would require modification to suit), or if I sort the data so that the text is at the top of the column, it works. Don't really want to do that either as there are 5 columns that this applies to and performing a 5 level sort shouldn't be necessary IMO. It seems that the paste is analysing the data and deeming that it should paste as a number even though the column contains text. There are only 120 rows of data, not a lot! I've tried redefining the field as Long Text (currently Short Text) but it doesn't make any difference.
View 2 Replies
View Related
Jun 24, 2014
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies
View Related
Oct 1, 2013
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?
View 3 Replies
View Related
Apr 28, 2006
Thanks in advance
Searched birthday in threads and couldn't figure this out.
I have a select query and I am trying to retrieve a birthday that is between two dates.
This is how I set up my expressions:
chkBirthDay: Format([BirthDay],"m/d")
Criteria for chkBirthDay Between Format([LaneDate]-3,"m/d") And Format([LaneDate]+3,"m/d"
So if LaneDate = 9/7/2005 I should be able to retrieve all of the Birthdays that fall within 9/4 and 9/10. Correct?
I do retrieve records but they are dates such as 9/17/1921 and 9/18/1948 but of course they do not satisfy the criteria. There is a date in the table that does, 9/7/1981, but that record does not show up?
Any suggestions?
View 3 Replies
View Related
Jun 14, 2006
Hi everyone,
I have a form with a date field on it. I would like the date field to automatically enter today's date, which it does now, but also I would like it to retrieve the previous record's date and if it is different from today's date, I want the form to use the previous date as the default value.
Does anyone know of how I would go about this?
Thanks,
Chris
View 5 Replies
View Related
Dec 22, 2006
Hi,
I create the table1 in SQL Server and then link the table to MS Access using ODBC. But, when I write do this statement
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from table1")
for i = 1 to rs.recordcount
msgbox rs.field(0)
next i
It only retrieve 1 record only, but the table have 10 records.
What's wrong with it?
I check rs.recordcount, it only loop one time.
Additionally,
it doesn't work this function in the ODBC link table
rs.addnew
rs.field(0) = "hello"
rs.update
And, when we use ODBC link tables,
we cannot delete data in the table.
currentdb.execute "delete * from table1"
it will give out error message, the table is read-only.
Please let me know about it, thanks.
View 3 Replies
View Related
Aug 24, 2015
i want to retrieve some data from a table i have pieced together a bit of code but get an error to few parameters.
Code:
Dim db As dao.Database
Dim Lrs As dao.Recordset
Dim LSQL As String
Dim Lname As String
'Open connection to current Access database
Set db = CurrentDb()
[code]....
when i paste the SQL into a query it works fine
View 8 Replies
View Related
Sep 29, 2004
What is the proper way to store in a field if I want to store current date AND time as a timestamp. I chose Date/Time as the data type and General Date in the Format. Now I wanted a timestamp whenever a new row is added so I placed =Date() in the default value but it only shows me the date, not the time?
Second question if you know Java: I retrieve this date in a java.sql.date and .toString() shows me the correct result in the default format but when i use the DateFormat.format() method on it, i get a Null Pointer Exception. What could be the problem?
View 1 Replies
View Related
Feb 19, 2006
I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.
View 3 Replies
View Related
Feb 1, 2005
Hi,
I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?
Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.
Thx,
Agamemnon
View 9 Replies
View Related
Feb 22, 2007
:confused: I need help desperately. I have two tables I am trying to comine in a query. Seems easy enough right? Yet the simple task has become impossible. I have two tables with the same feild for data, both containing 8 coulmns. Ex:
Table A-January
Columns
A B C
Data: 1,2,3
Table B-February
Columns
A B C
Data: 4,5,6
I'm trying to create a query that will contain the data 1,2,3,4,5,6 but am getting 11111111111,222222222,33333333333,4444444444,55555 55555,6666666, or getting 1,3,5. I've tried refining the data in many ways, even tried just created a table. Hand keying it in seems rediculous due to the end numbers being over 10,000. Any advice??? Please??? :(
View 14 Replies
View Related
Sep 27, 2006
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. Basically I need a list that has 5800 accounts, shows who has a phone number and who does not have a phone number. I have made a relationship between the two tables based on the person’s name. I used the code below and put the code in the “criteria” and “name” block. However, I received no results. When I remove the code from the query I receive only the results from list 2 (2300 accounts) and no data from list 1. Any Ideas?
FROM [5480 Master Data] INNER JOIN [5480 Meal Collection] ON [5480 Master Data].name=[5480 Meal Collection].name
View 3 Replies
View Related
Nov 13, 2006
I've been asked to make a DB for our production. We have 2 sources, of which have similar field names and collect completely different data, but can not be linked. I don't want them to be linked either, I don't think. The only thing I want to do is to take the 2 tables I have from my 2 sources and combine them into one. I'm using the first table in a make table query and then creating blank fields in the query also, so I can append the data from the second source in the "all production" table. The only problem I'm running into is that I can only get to 26 fields in the make table query, I need 31 fields to get everything. I tried creating a new query and not using any tables, and I was able to get to 16 fields. Is there something I'm doing wrong, or is there another way I can get a table created will all fields I need and append the data from both sources? I would need to be able to re-run this daily which is why I was trying to do it through a query. Thanks for any help/ideas. It's greatly appreciated!
View 5 Replies
View Related
