Combining Data Into A Single Field
Mar 17, 2007
REPOST from Design Area
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
View Replies
ADVERTISEMENT
Mar 10, 2007
Sorry for this totally stupid question but my memory is just not what it once was.
I need to separate one field into two, in a table that was imported from excel
Basically it has a size and a unit in each field
Example:
24.67 sy
26.84 sf
Thanks in advance for jogging an old man's memory.
I want to do this in a query not code if possible I remember something about a formula you can enter into the update to box in a saved query.
View 1 Replies
View Related
Nov 4, 2013
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents
Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents
Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String
[Code] ....
View 2 Replies
View Related
May 11, 2014
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
View 7 Replies
View Related
May 15, 2013
I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view
Job# __ ItemX __ ItemY
2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
View 5 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
Jun 12, 2007
I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.
I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.
Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)
View 7 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
Oct 24, 2007
I have a table of UPCs that we use for the tee shirts we manufacture. The problem is that our MRP system organizes the tables with S,M,L,XL UPCs in one row and the XXL UPCs in a seperate row(we use different pricing for XXL). I need to figure out how I can make it so that there is only one row per style number taht has both the S,M,L,XL,XXL UPCs in one row, but can't seem to figure out how to do this. I've attached in image for reference.
View 1 Replies
View Related
Feb 10, 2008
I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.
Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder
The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).
I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).
(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)
SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name
I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.
SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));
Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...
View 2 Replies
View Related
Jun 29, 2005
I have a table with all repair history in it. Unfortunately, the entry system is such that people can initiate a new record without typing in the correct customer name. So if my customer was "Fry's" it could be entered into the system as "FRYS" "Frys electronics"... etc. You wouldn't believe the ways people enter the same name. Anway, the only thing that links any of these records together is the item serial number. Though one serial number could be under a different name than the other.
Does anyone have any ideas as to how to create a temp table which associates related serial numbers to a single customer name? Even if the user had to select the customer names he want's associated that would be fine.
Note: I should mention that no there is no way to get this changed on the back end. I'm stuck with the tables I have.
Thanks.
View 2 Replies
View Related
Aug 19, 2011
I've been given the task of combining data from three locations into one database in Access, something that is relatively new to me. I'm trying to decide whether to have the locations enter data into an Access database or into an Excel spreadsheet at each location. Regardless of the program used to enter data, I would probably like to link each of the files to my main Access file and combine the three into one database there. I do not want the locations to add data directly to the main database if possible.
My question is, would this be done by an append query? If so, how? Also, my thought is that the data at each location would be added to every day. I would need to be able to add the NEW data only to the database without duplicating the previous day's data.
View 5 Replies
View Related
Oct 4, 2012
I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.
View 1 Replies
View Related
Jan 9, 2012
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies
View Related
May 12, 2013
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
View 2 Replies
View Related
Aug 8, 2014
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
View 4 Replies
View Related
Nov 20, 2013
Is it possible to append two queries? The queries have the same layout and labels...it's just a matter of combining the data into one datasheet view.
The two queries are below:
1)
SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], trp.Period, Round(prc.Index*trp.[Variable Value],2) AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.Len, trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel
FROM tblTRP AS trp, tblTRPpercent AS prc
WHERE (((trp.Indication)=prc.Indication) And ((trp.[Media Type])=prc.Type) And ((trp.Geography)="National") And ((trp.Month)=prc.Month));
2)
SELECT prct.DMA AS Geography, tv.Product, tv.Indication, tv.[Variable Name], tv.Period, Round(prct.percentUniverse*tv.[Variable Value],6) AS [Variable Value], tv.Outlet, tv.Daypart, tv.[Program Name], tv.Len, tv.Creative, tv.Campaign, tv.[Campaign Name], tv.[Media Type], tv.Vendor, tv.Channel
FROM tblTVOtherMetrics AS tv, tblDMApercent AS prct
WHERE (((tv.Geography)="National"));
View 3 Replies
View Related
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 3 Replies
View Related
Jun 5, 2007
Hello All,
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1
Any help would be greatly appreciated.
View 14 Replies
View Related
Apr 9, 2008
Hi I currently have 2 seperate reports that I want to use in one.
I have...
1. A Date report that works from a form with a to and from field, it then finds all reports between those two fields.
2. A Client report from a form, a simple drop down box that gets it info from a query, it then works of a macro to find all records to that client
What I want is to have the one form where you can 1st select the client at the top from the drop down and then you enter the to and from date, once you click ok it will bring up all records for that client within the dates.
This is the code I use for the dates, is there a way to add an extra bit that makes it look at the client combo as well to just show the records for that client between the specified dates...Code:Private Sub OK_Click()Dim strReport As String 'Name of report to open.Dim strField As String 'Name of your date field.Dim strWhere As String 'Where condition for OpenReport.Const conDateFormat = "#mm/dd/yy#"strReport = "clientnameanddate"strField = "DateJobReceived"If IsNull(Me.txtStartDate1) ThenIf Not IsNull(Me.txtEndDate1) Then 'End date, but no start.strWhere = strField & " <= " & Format(Me.txtEndDate1, conDateFormat)End IfElseIf IsNull(Me.txtEndDate1) Then 'Start date, but no End.strWhere = strField & " >= " & Format(Me.txtStartDate1, conDateFormat)Else 'Both start and end dates.strWhere = strField & " Between " & Format(Me.txtStartDate1, conDateFormat) _& " And " & Format(Me.txtEndDate1, conDateFormat)End IfEnd IfDebug.Print strWhere 'For debugging purposes only.DoCmd.OpenReport strReport, acViewPreview, , strWhereEnd SubPS I didnt write this code i just edited it for my own use so please reply in simple terms
Any suggestions are appreciated !!!
Thank You
View 1 Replies
View Related
Dec 13, 2012
I have a database that uses a field for the year (but I'm using a fiscal year that will end June 30, 2013) and another field as an autonumber. I use the year and autonumber as my reference number (i.e 2013-0001). I'd like to be able to combine these fields to generate the entire number. Also, I'd like for the year to add 1 beginning July 1st of each calendar year, and the autonumber start over at 0001 (i.e. 2014-0001 on July 1, 2013). Is this possible, and if so, how can I do it?
View 4 Replies
View Related
Apr 11, 2013
I have a datase that has the following fields.
Name
Comment1
Comment2
I have about 36 names and each of those names appears anywhere from 2 7 times each. Each name entry has a comment1 and a comment2.What I would like to do is create a form that allows me to choosea name and the display the comments 1 and comments 2 in text boxes.
I have made the form to allow me to choose a name and it shows one of the comment1 and one of teh comment2 in the adjacent text boxes as planned but, I want all the comment1s for Bob to show in the text box for comment1 and all of the comment2s for Bob to show in the Comment2 text box.I believe I need to concatenate the results in the comment1 and comment2 boxes, but I do not know where or how I would do that.
View 5 Replies
View Related
May 13, 2014
I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:
NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
NameColour
CarBlue;Green;Yellow
How i would do this.
View 5 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
Aug 20, 2007
hello people
im having some problems with my db. i have 2 tables(ATL and BATE)
in ATL, i have (im, IMAGEID, BREAK, TASK, LOC, bate) and in BATE, i have (Bate, var)
im trying to take Bate from Batetable, and insert it to ATL, even tho thers already a bate in ATL, both contain different records...
i tried this, but didnt work
Select ATL.im, ATL.IMAGEID, ATL.break, ATL.task, ATL.loc, ATL.Bate, BATE.bate
from ATL, BATE;
it worked, but BATE shows the same value for the hole row, it displays the same thing thing, even tho each row has a different value...
can anyone help me out?
thanks
View 5 Replies
View Related
Jan 16, 2014
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectB
UserA - Hours - Date - ProjectB
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
View 12 Replies
View Related
