Too Many Columns In My Table, Need Help.

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 Replies


ADVERTISEMENT

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Autofill Columns Of Table With Values From Another Table

Jul 10, 2006

Hello

I have two tables tblCList and tblCode. The primary key of tblClist is an autogenerated number which is the foreign key to the tblCode.

The tblCList has another column names sClist.

whenever I import values from Excel into the tblCode, I need Access to look up the text values from the sClist in the tblClist and insert the appropriate number corresponding to the entry in tblCode.

Please Help


Thanks

View 2 Replies View Related

Possible For Primary Key Of One Table To Act As Foreign Key In Another Table For More Than One Columns?

Nov 7, 2012

Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!

View 6 Replies View Related

1 Table 2 Columns 1 Form

Mar 17, 2006

Hi all!

First of all sorry for my terrible English! ;)

I have a very simple question (I think). I have a table with 2 columns in it, filled with city names and their id's. For example:

city - id
rotterdam - rd
amsterdam - ad
new york - ny
london - ld

I have a form with this city-table as a dropdown menu to fill in the control. What i now want is, when I select the city, a different text field named ID must be filled in with the corresponsding city-id.
So, how do I fill in 1 field when a edit the 2nd field?

Thnx in advance!!

View 1 Replies View Related

1 Table 2 Columns 1 Form

Mar 17, 2006

Hi all!

First of all sorry for my terrible English! ;)

I have a very simple question (I think). I have a table with 2 columns in it, filled with city names and their id's. For example:

city - id
rotterdam - rd
amsterdam - ad
new york - ny
london - ld

I have a form with this city-table as a dropdown menu to fill in the control. What i now want is, when I select the city, a different text field named ID must be filled in with the corresponsding city-id.
So, how do I fill in 1 field when a edit the 2nd field?

Thnx in advance!!

View 10 Replies View Related

Maximum Nb Of Columns In A Table

Feb 11, 2008

I would like to know if there is a maximum nb of columns to use in a table or at least a prefered max nb. For example i have a table "customers" and inside i have "name" "last name" "age" "gender"..and so on is there a limit?
By the way i'm using access 2007

Thx for the explanation in advance.

View 3 Replies View Related

Calculating The Sum Of Two Columns In One Table

May 18, 2005

I have this statement that doesn't work...

UPDATE table1 SET table1.val1 = [table2].[val2]+[table2].[val3];

How can i add two columns of the same table and update my query?

Thank you in advance!

View 1 Replies View Related

Modules & VBA :: Union All Query - Transposing Columns To Rows With Variable Columns?

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

Concatenate Two Columns In Table Design

Apr 25, 2008

I have created a Table Design
Name
Last Name
ID Number
NameID

I import data from CSV xls file which has Columns of Name, Last Name, ID Number.

What I want to achieve is while importing it should Concatenate Name and ID number and store in Name ID.

I have searched it well but havent been able to find solution. Any suggestion how to achieve this.


Thanks
NIRI

View 4 Replies View Related

Adding Lookup Columns To A Table

Sep 21, 2015

In the tutorial listed here: [URL]....

It states after opening the database in Datasheet view :

In the Navigation pane, double-click the table in which you want to create the lookup column - this opens the table in datasheet view.

On the Datasheet tab, in the Fields & Columns group, click the lookup column.

And it show you some icons.

View 1 Replies View Related

Combining Some Columns In Access Table

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

Table With Three Fields - Rows To Columns

Jan 28, 2012

Rows 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?

View 1 Replies View Related

Select Distinct From Multiple Columns In Same Table

Sep 7, 2006

I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.

The following works for one column:

SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";

Are there any suggestions?

Thanks

View 2 Replies View Related

Compare Values Of Columns Within The Same Query Table

Nov 19, 2006

Hi,
I am trying to compare two columns' values within the same QUERY table, but I kept on getting the "Data type mismatch in criteria expression" error. What am I doing wrong?

Here is my Select Query Statement:
SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.[Employer Actl], qryDedparmDedetail.[Admin Amt], qryDedparmDedetail.[Admin Actl], qryDedparmDedetail.[Employee Actl], qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType
FROM qryDedparmDedetail LEFT JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT
WHERE (((qryDedparmDedetail.[Employer Amt])<>"Employer Actl") AND ((qryDedparmDedetail.LeftType)="01"));

View 2 Replies View Related

Parsing String Into Separate Columns Of A Table

Sep 3, 2007

:confused:

I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:

Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"

Is there a simple VB funtion to accomplish this?

View 1 Replies View Related

Queries :: Count Multiple Columns From The Table?

Aug 26, 2014

I want to count data from my table ( Table ) and to display result like a dashboard (Desired result ). To count this do i need to write query for every column and then link to final column? i have totally 300 columns to count I am attaching Table & desired result images;

View 5 Replies View Related

Tables :: Combining Two Text Columns Into One In A Table?

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

Queries :: Transpose Table Columns Into Rows

Jun 18, 2013

Is there a way to create a query that will transpose table columns into rows:

Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy

Is there a way to display the table in a single row separated by commas:

xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy

View 11 Replies View Related

General :: Query To Separate Columns In Table

Aug 14, 2014

I have a table tblItemSold

ItemNum Date Sold
1111 Aug-10-2014 25
1111 Aug-9-2014 24
1111 Aug-8-2014 23
2222 Aug-10-2014 11
2222 Aug-9-2014 12
2222 Aug-8-2014 13
3333 Aug-10-2014 5
3333 Aug-8-2014 3

I want to write a query and transform above into

ItemNum Aug-10-2014 Aug-9-2014 Aug-8-2014
1111 25 24 23
2222 11 12 13
3333 5 3

View 3 Replies View Related

Tables :: Adding Same Prefix To All Columns In A Table

Jan 23, 2013

My database has three tables with many columns. The three tables are identical in the names of their columns.

I want to copy all columns from all three tables together into one single table, giving the respective columns prefix table1-, table2-, table3- since the columns would otherwise be indistinguishable.

I already tried to search the board for "table columns add prefix".

I use Access 2010

I managed to copy all columns together into one table through design-view, but cannot figure out the "add prefix" step.

View 9 Replies View Related

Queries :: Multiple Columns From Table Into One Column

Oct 14, 2014

I am working with other data that has been created by someone else.

There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.

So far I have tried using this SQL:

SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T

[Code] .....

But it is not working - Is it to do with the field names or am I entering something incorrectly?

View 8 Replies View Related

General :: Table Import From Excel - Only 52 Columns?

Sep 5, 2012

I'm having problems importing a spreadsheet from Excel for a client.

They are using Access 2003 and Excel 2007, 3.5GB RAM

It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.

The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.

It returns error 3274: "external table is not in the expected format."

There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.

The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.

if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this.

View 1 Replies View Related

General :: Calculated Columns Based On Another Table

Aug 18, 2012

Is it possible to have a calculated column based on another table. i have a 1 to many table. the table that is the parent hold a start time. all of the 'many' have an individual time length. i would like a calculated column in the parent table showing the full length. is it possible or will i have to use an update query?

View 1 Replies View Related

General :: Inner Join - Copy All Columns From Table

Jun 24, 2013

I have some code. I want to copy all columns from

Code:
tabela_zrodlowa = "tblGoraZlecenia"

but only one column which is called "Id_rodzajpracy" from

Code:
tabela_zdrodlooffset = "tblOffset"

How could I modify red instruction to do inner join?

Code:
Dim rec_GoraZlecenia As DAO.Recordset
Dim rec_GoraZlecenia2 As DAO.Recordset
Dim rec_GoraZlecenia3 As DAO.Recordset
Dim tabela_docelowa As String
Dim tabela_zrodlowa As String
Dim tabela_zdrodlooffset As String
Dim s As String

[code]....

View 5 Replies View Related

Merge Columns From Many Tables Into One Column In New Table

May 21, 2013

I have 4 tables that store information on guest charges (stayinfo/room rate, fuel charges, bar tab, merch charges) each linked to table carrying guest ID's. Is there a way to combine/merge all the tables into one in such a way that all of the guestID's are in one column, all of the charges are in another column etc? Basically adding one table to the bottom of another.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved