Creating Mulitple Columns - Please Help!!
May 3, 2007
Hi There, first time on here, so hopefully i can get an answer to my problem.
i am trying to split data in a sigle column accross multiple columns. basically i have a table like the one below:
Member DATA
A 34
A 65
B 234
B 456
B 22
C 88
X 6
X 34
Y 23
Y 353
Y 25
Y 9
Z 45
so what i want the query to do is to show a list of Unique Members, and then split the data column so it show an entry for each Member accross mulitple columns.
is this possible? i have very limited access knowledge, so a comprehensive answer will be very much appreciated.
View Replies
ADVERTISEMENT
Jan 1, 2005
I am having trouble getting ASP code to create a new column in an Access database.
This is what is currently coded, but does not work. Something tells me that the code should be Create instead of Alter, but not sure what the correct terms would be. I need the code to automatically create this column called PCName with the listed properties in the table called member. Any help would be greatly appreciated.
re.Open "Alter Table in member Add Column " & PCName & " varchar(50) default None",conn2
sql = "Update member Set " & PCName & " = 'None'"
View 1 Replies
View Related
Nov 26, 2014
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
View 5 Replies
View Related
Jan 3, 2013
Number of Operators per Permit
Permit
Operator Name
6
065-24088
EOG RESOURCES INC
6
065-24088
PDC MOUNTAINEER LLC
[code]....
I want to take this data and create six new columns (operator1; operator2; operator3; operator4; operator5; operator6) for each permit number so that each unique operator name will be housed in each of those six columns, with the rows being defined by the permit number. So, I want to produce something that looks like this...
Permit
operator1
operator2
[code]....
At present, there are as many as 6 operators per permit, but the number of operators per permit varies from 1 to 6. I included an example that has 5 operators, to show that I want the sixth column to be null in this instance. So if a permit had only one operator, only the operator1 field would take on a non-null value in the new table/query.I know how to do this with 2 operators per permit by using min/max or first/last functions in a query, but I don't know how to deal with more than two operators per permit.
View 4 Replies
View Related
Nov 10, 2005
Hello I am new to Access and slowly finding my way round.
I have built 30 queries which eventually build two Union Queries of which each query as a slight different criteria.
In all 30 queries there is an indentical Field name called Arrived. What i want the user to be able to do is change the criteria in the Arrived figure throught a message box. So far the criteria is in the field is:
>=date()-3
This will show me everything 3 days ago. What i want to do is to let the user check what happened 5 days ago 10 days ago. I know i can create the following to solve the message box:
>=date()-3-[Enter in the days less than today]
Is there away to update all 30 queries at once with the same code?
View 3 Replies
View Related
Jan 11, 2005
Hello.
I'm trying to write a database for basic manual tests.
Each test has multiple steps.
My problem is: How do I replace the step details when my user has clicked "next step" without losing any of the other information present on the form?
Thanks
Katrina
View 2 Replies
View Related
Feb 10, 2006
Hey guys, my problem is this:
I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
Thanks in advance
Nathan
View 3 Replies
View Related
Jun 14, 2005
Hello,
I am trying to create a listbox that shows "calculated" dates for a two week payroll. I have a field in a table that is set to a start date (05.31.05) and another field that specifies the number of days between dates (14). I would like set a listboxes recordsource to show the start date and then calculate the next 4 dates using the first date (05.31.05) and the range (14) without actually storing any dates in the table. I can figure out how to do it if the values are stored, but I just want to know if this is possible to get the results another way.This may sound crazy and unecessary, but I want to know if this is possible without storing values in the table.
Listbox values:
05.31.05 - (first date from table)
06.14.05 - (first date + 14)
06.28.05 - (first date + 2*14)
07.12.05 - (first date + 3*14)
07.18.05 - (first date + 4*14)
Is this possible? HTMS. Thanks.
View 5 Replies
View Related
Feb 18, 2005
I am trying to make a user friendly form where the user can select mulitple items from a list box and then from that selection this info is listed in another table (Form) where they can add additional info.
For example,
List box lists Products to be made (1,2,3,4)
If I choose item 3 and 4 i want them to be listed in a seperate form where I can add more info (quantity).
Any help or other suggestions is appreciated.
View 3 Replies
View Related
Aug 20, 2004
I have created a form in access that contains a multiple select list box and a command button "New Record," that creates a new record. I select values in the list box for the current record. When I click on the "New Record" button, the values that I selected in the previous record are still selected. How can I reset the list box so that no values are selected when I create a new record?
View 8 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
May 14, 2014
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] ....
View 2 Replies
View Related
Oct 30, 2007
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my totals column? Basically, I want to add the figures found in several cells that precede it?
View 4 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
May 20, 2015
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
View 3 Replies
View Related
Jul 31, 2013
I 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.
View 12 Replies
View Related
Feb 19, 2008
Hi All,
I have a Append query which has columns from two tables. Now i need to create a new column "Sum" in new table which should Add two of the Number columns .
That is : I have column A and Column B; I need to create a Column C which contains (A+B)
Please advise.
Thanks,
Kon
View 8 Replies
View Related
Jan 24, 2007
Hi,
I'm new at access but getting the swing of it. It is a pretty cool toy.
I have a table that I am adding various columns of data. Once added I move the columns to where I want them to be. I am hitting the little save disk on the top toolbar but when I reopen the file the columns are not where I placed them. I'm not sure why this is happening. Any thoughts?
Thanks in advance for your help.
Eddie.
View 6 Replies
View Related
Dec 30, 2004
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar
View 6 Replies
View Related
May 6, 2006
Hi I have search the forums for an answer to this..i have 4 fields address 1, 2,3,4 and i would just like to all all the information to one field call address..how can i merge the information..please bear in mind i am useless at access..i thought there might be a way of copying the columns and just having a merge function to do so ..but cant find anything...please help..Williebear:(
View 4 Replies
View Related
Aug 22, 2006
Hello again!
How do I prevent the information in lookup columns from showing up in other tables, queries, etc... as numbers.
Example.
Lookup to Fruits. Where the records for fruits are Apple, Banana, Orange, and Pear.
The lookup column lists all the items {Apple, Banana, Orange...}, but in queries the data just appears as numbers {1, 2, 3}.
Many thanks for anyone's help in advance!
View 5 Replies
View Related
Dec 22, 2006
Hello.
Here's some background on what I'm trying to do. I am building a database that will hold data on 31 parks. Each park will contain many advertisers and each advertiser may be in one or many parks. Theres more to this but i will just stick to what I am having trouble with.
The tables i have made thus far pertaining to my question are:
1. "Parks" table. It has two cols one for ParkID (key) and ParkName
2. "Advertisers" table. It has 9 cols for things such as AdvertiserID (key), addresss, etc...
3. "AdsData" table. This is the problem table. It contains AdID (key) and ad content columns (copy, names, driving directions, etc...). In addition to the ad content however, I need each ad to indicate which of the 31 parks it is located in (one or many) and which of the 16 park activities (things like hiking, swimming...i have another table for these) it is to appear in (one or many).
The only solution I have been able to come up with is to make a table ("AdsData") with 55 columns in it. In addition to the ad content data for each ad, there are 16 yes/no columns for selecting one or more activities for the ad to appear in and 31 yes/no columns for selecting one or more parks for the ad to appear in.
This seems excessive because I have a table with all the Parks in it and I have a table with all the Activities in it. Plus I've been told that having so many columns in a table is a bad, bad thing.
But in my limited knowledge of Access, this is the only solution I can come up with.
Is this the only way to do this?
View 3 Replies
View Related
Jul 30, 2005
I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:
Budget Total Expenditures Balance
Thanks.
View 10 Replies
View Related
Dec 3, 2005
Hello,
I cannot find anyway to do a querry search in different columns.
For example:
a movie database, a movie has more genres, so i have a column for genre1, genre2 and genre3. now i want to be able to search in all three columns at the same time for 1 genre, is that possible ?
View 1 Replies
View Related
Jun 15, 2006
If I have a date column and another column with another consistant date, Can I make a criteria to compare one to the other. Ex:
END_DATE Today's_DATE
06 Feb 05 15 Jun 06
12 Feb 05 15 Jun 06
01 Jan 06 15 Jun 06
26 Mar 06 15 Jun 06
18 Aug 06 15 Jun 06
28 Dec 06 15 Jun 06
Can I make a criteria that will only show the END_DATE data that is before Today's DATE? Does it need to be a new column or can I place it in the criteria of one of these column.
View 1 Replies
View Related
Nov 14, 2006
Hi
I have a confusing situation. I have a need of creating a combobox which displays records from different tables.
Eg.
I have a table called "house parts" and filled with records like
room, hall, garage
Secondly i have a table called "Parts" and filled with
floor, ceiling, lamp, window etc.
As u see, "house parts" could consist of "Parts" like "room" could have floor, ceiling etc.
I need to create a query that shows me all the records from "house parts" and also "Parts" in the same list.
Can anyone help me please
View 4 Replies
View Related
