Replicating Fields In Rows
Feb 15, 2007Does anyone know if you can replicate the field values in rows automatically?
e.g.
NetworkHandsetTariff
3K800iVTT700
to become:
NetworkHandsetTariff
3K800iVTT700
3K800iVTT700
Any ideas?
ADVERTISEMENT
Report Assistance- Linking Like Fields Without Replicating
Nov 17, 2004I am trying to generate a report that using 2 different tables, related by 1 field. Here's an example of what I'd like to accomplish..
a.... red....Friday
B.....blue...Tuesday
C..... red.... Tuesday
D.....red....Friday
E.....blue...Tuesday
Report
Red
A......Friday
C......Tuesday
D......Friday
Blue
B.....Friday
E.....Tuesday
What it's doing now is:
Red
A......Friday
Red
C......Tuesday
Red
D.....Friday
Blue
B.....Tuesday
Blue
E......Friday
I want to link the one field (ex. blue) so that it simply lists all of the occurances together. I tried the Access Help, but I can't seem to find the right string.
Thanks!
Graphs Replicating
Jun 13, 2005Hi All,
This is a simple problem i think....but am struggling to find a variable to change!.
The problem is that i have a chart on a form, but the chart is replicated 22 times (i.e 22 charts on seperate pages)...there should only be 1!
I have 22 variables on the X-Axis...so am presuming it is linked to that somehow. am also graphing off a crosstab.
I am still searching for a solution myself...but thought i'd ask in here incase anyone has any ideas/experience of this problem!
Thanks!
One To Many (BookingID) Not Replicating To Other Tables
May 18, 2012Attached is the db.
tblBookings.BookingID is not being replicated to tblBookingDetails.BookingID
Other future tables (tblRooms) will also need this BookingID?
Many customers book several times a year and use different products.
Not much different than a sales database showing customer purchases (lifetime history).
General :: Replicating Excel Function
Oct 10, 2014Is it possible to compare rows, ie in excel I could do =A1=A2 to compare certain fields then if they are different/the same highlight.Is there a way of doing this in access?
View 1 Replies View RelatedTable With Three Fields - Rows To Columns
Jan 28, 2012Rows to columns;
I have a table with three fields
ID,Desc,Code
1,aaa,code1
2,bbb,code2
3. aaa,code3
4, bbb,code4
I need a query creating these two columns:
Desc,Code
aaa,code1;code3
bbb,code2;code4
how can I do this?
Combining Fields From Multiple Rows Into One Row & Field
Jun 5, 2007Hello 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.
Delete Query To Target Fields Not Rows?
Sep 5, 2007Hi
I need to know if it is possible to create a delete query which will target particular fields in a row based on certain criteria in a seperate combo box(on same table).
The few ways that I have tried to do this have been unsuccesful so if anyone can shed some light on the subject it would be greatly appreciated.
Automatically Add Rows To A Table Or Fields To A Query?
Mar 23, 2012I am using Access 2007. I have an ODBC source feeding data into a table, to which I made a crosstab query that displays the useful data from that source. I need a way to add data(new field), using a form, that is a associated with the lines in the query. I've tried creating another table that uses the sample ID as the primary key, but I need it to automatically create a new row for each query line. Is there a way to do this, or better way to add data to a query. Examples are below.
The ODBC data looks something like this
SampleID PeakName Conc
sample1 THF 50
sample1 MEOH 10
sample1 propanol 25
sample1 butanol 15
sample2 THF 21
sample2 MEOH 15
sample2 propanol 17
sample2 butanol 23
I have the query look like:
SampleID THF MEOH propanol butanol (I need to be able to add a field here)
sample1 50 10 25 15
sample2 21 15 17 23
Linked table I created:
SampleID LIMS #
sample1 1234567
sample2 4567891
The problem is, for this table I have to manually select each sample ID. I want it to automatically add the sample IDs as new ones are querried.
Access 2010 - Table With Same Fields And Rows
Aug 24, 2011i'm using Access 2010 and i created a main form , from which i want to let users to open new table with fixed fileds and rows, also the name of the table will be as per user choice. so if a user want to open new table from the main menu, he will have the choice to press on a command button and a dialoge box will ask to name the table. the new table will then be open with the same fileds and rows names( name only) . the new table will be edited using a form.
View 3 Replies View RelatedHow Do I Create A Query To Move 2 Fields On The Same Row To Be 2 Rows In One Field
Jun 6, 2007Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian
How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
Thanks so much!
Jenny.
Getting Data From Rows To Columns Based On Common Fields
Jul 30, 2013I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
Key On Multiple Fields - Highlight Rows With Matching Keys
Dec 29, 2011I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
Queries :: Identifying And Combining Rows Based On Matched Fields?
Jul 10, 2013I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
Automatically Filling Linking Fields For New Rows In Related Table?
Dec 18, 2012OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
Compare Several Hundred Rows Against Other Rows
Nov 21, 2005Not sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
Thanks!!!
Converting Rows To Columns And Columns To Rows
Jul 31, 2013I 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.
UPDATE Rows
Sep 11, 2005Hi Im trying to update a row this is the SQL im using
UPDATE HouseEntry
SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1)
WHERE
EntryNo=40;
When I run it I get an error saying "Operation Must Be an Updatable Query"
If I physically put it in the date e.g SET [Date]=#09/15/2005# It works
so there must be a problem with my Select Statement.
Any ideas???
Cheers
Bikeboardsurf
Repeating Rows
Dec 25, 2005hi
my problem is when i export data form table or query to textfile it give me repeating rows my computer is win me
help me
Individual Rows
Mar 23, 2007Hi,
refer to the (record.jpg)
the whole record is identified as " rst.RecordCount " ( red color box).
how to identify each individual rows ( blue color box)?
Run Out Of Table Rows
Jun 1, 2007The following is for a successful pet grooming business.
I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed
Max # Rows In A Table
Jun 14, 2007I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?
View 4 Replies View RelatedYes/No Boxes In 2 Rows
Mar 11, 2008Hello there,
I am making a roster for 25 people. In my form, the start of the roster is 0700hours until 1900 hours (increments of 15 minutes each) so I have 48 check boxes, which are ticked when one is rostered on for that time.
I am getting crossed eyed looking across the screen at these 48 tick boxes each for Monday through to Friday.
I have searched high and low and cannot find anything on how to have 24 check boxes (7am to 1pm) in one row and the other 2 dozen (for the next 6 hours until 7pm) on the next row, to be less painful on the eyes.
Can this be done?
I would be grateful for any ideas on how to start.
Thank you.
Regards,
Ligaya
Selecting Rows
May 17, 2006Hallo Everyone,
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
aravind.s
Return All Rows
Aug 16, 2006I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
Any help appreciated!
Dave
Deleting Rows
Aug 30, 2006Hello, Does anyone have any suggestions on how to delete certain rows? For example rows 10,000 to 17,000. I know in MS SQL server you can use Rowcount, but I believe there is no Rowcount in Access?
View 1 Replies View Related