I need to know how to combine two records. What I have is a text file that is imported into a table. The problem is, the text file has 4 fields on one line then 3 fields on the next line. Is there a way to combine these two lines into one record? I do not want to do this in the text file. I want to import the file and run some code to combine the two records into one record, delete the second line, and go to the next two records. What can I do? Sorry for any spelling but I have to run. Thanks for any help.
Hi,all There are 3 records of my table: DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10 38 103 1012 22 2133 33 39 103 7 9 99 40 103 10 20
/"DBr" shows how fields should fill/
I want combine these 3 records to one record. What i need to do ? DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10 38 103 1012 22 2133 33 7 9 99 10 20 thx
I have a DB table that contains two fields: Project and Minutes. The time tracking software records each time I punch in or out under a certain project but records multiple instances for instance:
I want to combine the records that have the same project. Maybe a "make table query"? I am not sure. If anyone could offer any assistance I would be very greatful.
So I have two tables. One table "tbl_SRVASSET" lists out servername, serialnumber and asset tag. Second table "tbl_SRVNIC" list out the same servername, IPAddress and IPAddressTypeDesc.
The issue is that I have records in the "tbl_SRVNIC" with the same server listed numerous times (because it has 2 or 3 NIC's). So there is obviously a one to many relationship betweeen the two tables (common I guess). What I'm trying to do is be able to take the servers in the "tbl_SRVNIC" table and a new table or create a query with a single record for that server and have all 2, 3, 4 or maybe more IPAddress's assigned to them listed in the same record. This is what I'm having trouble with...
The end result is that after I've got this new table that lists only a single server and it's associated NIC's in the same record I can then combine that data with the "tbl_SRVASSET" via a basic query so my end result is a brand new table "tbl_SRVDETAIL" that looks like this: SERVERNAME, SERIALNUBMER, ASSET, SERVER NIC1 IPADDRESS, SERVER NIC1 IPADDRESSTYPE DESC, SERVER NIC2 IPADDRESS, SERVER NIC2 IPADDRESSTYPE DESC, SERVER NIC3 IPADDRESS, SERVER NIC3 IPADDRESSTYPE DESC, (and maybe more columns if there are more, but that is a variable as some servers have 2 NIC's, Some with 3 or more so...).
I'm a new Access user. I'm looking for an easiest solution to combine more records into one, using comma delimited. Let's say that I have a table T1 with two columns Code and Client like this:
Code Client X C1 X C2 Y C1 Y C3 Z C1 Z C2 Z C4 Z C5
I need to see those records in a Query, grouped by "Code" with the Client's value combined like this:
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code: SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC FROM [1733_All Print Orders] WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013# GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
Product. Item Description A It's Red B It's Blue C It's Green D You'll love it
Category. Item Category_name A Cat1 A Cat2 A Cat3 B Cat1 B Cat4 C Cat1 C Cat6
I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:
ItemCategoryConsolodation. Item Desc Categories A It's Red Cat1,Cat2,Cat3 B It's Blue Cat1,Cat4 ...
How does one do that?!
What's it called when you try to do this so I can Google it?
I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?
I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
I have a question that I don't know how to really explain, so here is an example of the table I have:
35510157.32 355102267.44 35510372
The first column (35) is Employee ID. The second column (5101) represents a certain time code. And the Last Column (57.32) represents an amount of time.
I am trying to create a query that puts the data like this:
3557.32267.4472
There is about 3500 different Employee ID's and each ID can have up to 10 different time codes. Is there a way of doing this without doing a Make Table or Update query?
My application links to 3 mdb (eg. 1a.mdb, 2b.mdb, 3c.mdb) backend database.
It works as the following: if an order is placed by a company (public sale), the order will be stored in 1a.mdb, if the order is for private sale, the order will be stored in 2b.mdb. The items for public sale are stored in 1a.mdb, the items for private sale are stored in 2b.mdb. It means an order cannot combine both public and private sales, but in fact this always an issue. 3c.mdb is used for storing common data, such as customer details.
Now I would like to create an order that includes both public and private sales. How I can do that? Do I have to combine 1a.mdb and 2b.mdb into 1 database?
I was wondering how to go about combining both Access and SQL together. Am I going to write the SQL codes in VB and then Access will know how to combine the tables I've created with the SQL codes?
Hello, I have around 40 tables right now on hand and I would like to combine them into one big table (a table, not table formed by query) and I am wondering is there a easy way of getting it done without me physically copying and pasting all 40 tables? Thanks!
Hi I have Two tables with identical field names with information reagarding a physical catalogue from one site and a catalogue from another site I would like to join them utilizing there manufactorer code. And ignore all the duplicates.
SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname HAVING (((tbl_subscriptions.subscription_fee)=0));
This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0
TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member WHERE (((tbl_years.year)>Year(Date())-"6")) GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone PIVOT tbl_years.year;
This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from WHERE (((tbl_years.year)>Year(Date())-"6"))
My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed? Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5
I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.
The first query simply pulls certain records from a table:
SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume FROM Sensor5 WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));
The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:
SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume FROM SpeedWeekday5NB GROUP BY SpeedWeekday5NB.SensorTime;
Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!
I would like to take these two queries and combine them into one if possible. This is the first query:
SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1] FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));
And this is the second query which is based on the results of the first query:
SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2] FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0)) GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
Can this be done and if yes can someone show me how? Thanks
When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.
When I run my query, I get a different record for each. So for the above scenario, I may get 12 records. Problem is I only want one record for this event. Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma? If I have to copy the data down locally, that's okay. I am wondering if an Update query could be used somehow, but I am not sure how to do this.
