Newbie Question - Repeating Fields In Tables

Mar 1, 2006

Hello hello. I'm new here, and new to Access... I've just started taking Microsoft's Office Online training tutorials and I was wonder about planning tables and repetitive data.

I had transferred some stuff from Excel to Access but now I'm looking at redesigning my whole database using relationships, etc.

I'm mostly working with a mailing list, so my fields are:

Mr./Ms.
FirstName
LastName
Title
Company
Address
City
State
Zip

Okay. There is also a second list of contacts that will link to the Company field. But my question is, most of the contacts in this list have the same title (be it President or CEO or whatever...) -- there's only like four variations.

Would I make a separate table with just those four titles, so I don't have to repeatedly type "President" in the title field?

And then if so, what about things like Mr./Ms.? It's only two variations and every record needs one. And what about State? Again, only two states ever. These don't get their own little "related" table, do they? And if not, how do I avoid typing it every time??

Thank you so much for reading this, and thanks in advance for your help.

~Jas/LadyGrey

P.S. I have another major question too, actually. As I said, I'll have two different lists hinging on the Company name. Except I have some companies with multiple contacts... so I don't think I can make the Company the primary key, since it may be repeated for a few records. Help, please! Thanks!

View Replies


ADVERTISEMENT

Return Only One Record From Table With Repeating Values In Fields

Nov 7, 2006

I have a table like this


Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...


I want to do a query to return just one row per unique ItemNo

So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog

I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.

I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.

View 3 Replies View Related

Newbie Need Help Fields Keep Cloning On All Records

Jun 16, 2006

Newbie Need help !!!!!!
downloaded a template from microsoft for contacts played around with the form to suit myself...
the problem i have is when i enter any thing in the text box this is cloned to all records this is happening with all text fields im not a programmer so plz go easy!!!!!!
thank you

View 1 Replies View Related

Newbie Need Help Fields Keep Cloning On All Records

Jun 16, 2006

Newbie Need help !!!!!!
downloaded a template from microsoft for contacts played around with the form to suit myself...
the problem i have is when i enter any thing in the text box this is cloned to all records this is happening with all text fields im not a programmer so plz go easy!!!!!!
thank you

View 1 Replies View Related

Some Newbie Queries About Choice Dependant Fields

Aug 7, 2005

I have a database i am playing around with in Access 2003

I should state im new to Access

Anyway i have a field with a dropdown menu with the choice of Yes or No

I want another field to be populated from an entry in the table Extras called ElectricityCharge but only when the Yes is chosen above

Had a look around and cant see any sort of If then type function or anything of that ilk, how can i do this ?

View 4 Replies View Related

Finding Specific Fields In A Record (sorry, Newbie!)

Sep 5, 2005

Hi everybody,
My question may sound you stupid but i am a newbie and i think you can help me...
My problem is,
I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record...
Any help will be greatly appreciated...
Thanks...

View 1 Replies View Related

Newbie Help W/ Forms And Tables

Apr 20, 2007

I'm having a problem with how my forms add data to my tables. I've been using the wizard to create 3 different tables and a form to update them. One of two things happens to me each time I finish the form, either it won't allow me to input any data or instead of updating a field each time I put something new it will write over the existing data for that field. I've tried using sub-forms too with the same problems. Is there something I can do to fix this or is there a past post that I can look at that may help me out.

Sorry if this isn't too clear like I said I'm new to Access and this is really only my second time using it.

View 3 Replies View Related

Newbie Help! Primary Key Not Carrying Through Tables

Aug 1, 2005

I have constructed a DB for work to record accidents, however I am running into problems. On each form I have a button that Saves the data closes the current form and opens the next form, depending on what data was entered. I have this ruuning fine using this code,

Private Sub SaveRecord_Click()

Dim NextForm As String

If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If

DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm
End Sub


Private Sub SaveRecord_DblClick(Cancel As Integer)

Dim NextForm As String

If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If

DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm

End Sub

The primary key of this form is "PersonKey" and the two forms taht could open, each have"PErsonKey" as a foriegn key. However the key does not get brought across into the next form and the save function does not appear to work on all forms... :confused:

I put another button on this form using the control button wizard, and it gave me this code,

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub


However when I run this from the form, it tells me that the "PersonKey" field needs a value.... :confused:

View 6 Replies View Related

NEWBIE HERE...Two Tables.. Want To Delete Duplicates Between Them

Apr 3, 2006

I think this is an easy one...

I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:

DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID

I'm being told I need to define the table to delete from... I thought I defined it.

Thanks!

View 2 Replies View Related

Newbie Link Tables Problem

Jan 11, 2005

I'm building a simple db to track people with access to systems and buildings (Access 2000). I have three tables:

1. People (names, ID, etc.) with a primary key of an autonumber
2. Bldgs - y/n fields of bldg #'s with a primary key of a number (linked one to many from the People table)
3. Systems - essentially the same set up as Bldgs table (though they're not related)

People may have access to either bldgs, systems, both or none.

So I build a query to combine all three tables. If I add a new record (to the People table and only one of the other two tables) I can't go back later and add data to the third linked table seemingly because the autonumber exists in the other two tables but not that one. Well, sure.

So the question is, I'm obviously missing something insanely obvious, or if not, how do I get around this? It's driving me nuts.

Thanks,

View 4 Replies View Related

Repeating Rows

Dec 25, 2005

hi
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

View 2 Replies View Related

Repeating Query

May 12, 2006

As posted here
http://www.access-programmers.co.uk/forums/showthread.php?p=489795#post489795

I thought its better to mention it here as well:

How do I get the query to repeat itself to gernerate extra rows of info.

I have "[Enter Ingredient Code]" in the criteria section on one column, this gives me a popup when starting the report to enter the code. I would like to have this reappear to enter more information. Maybe the action to with another pop up box like

"Would you like to add another ingredient"
"Yes" "No"

Click on yes and the query will rerun and enter the data in, select no and the report is made.

M

View 1 Replies View Related

Help With Repeating Records

Nov 29, 2006

My table has the following fields

ItemNo Name ImageName DateRecd
123 Foo 00123.jog 1/1/06
123 Foo 00FOO.jpg 10/11/06
123 FOO FOO123.jpg 7/5/04
456 bar 00456.jog 6/10/06
456 BAR 00BAR.jpg 7/5/00
456 Bar BAR456.jpg 11/23/06

How would I write / get a query to run and show me, the 2nd item numbers, the 123 but only the most recent date so 10/11/06 and for itemno 456 the item dated 11/23/06

View 5 Replies View Related

Repeating Data

Dec 6, 2006

Here we go again i have another problem.

I am finding it very hard to explain my problem so i have attached a copy of my database:

Have a look at NUMBERPLATE OPTIONS in the booking table. When you click on this field you have to type in the END DATE and the CAR TYPE to generate a list of numberplates that can be selected. This END DATE and this CAR TYPE have already been enetered into the BOOKING TABLE for this particular record. Is there anyway of repeating this data entery?

Iain

View 4 Replies View Related

Repeating Information On New Record

May 25, 2007

hello,

I have a form, and I am on record "A". When clicking to add a new record "B" I want all the same information on record "A" to show on the new record "B".
How can I do this without using default?

Thanks

View 8 Replies View Related

Repeating Names In Combo Box

Aug 6, 2007

Hello to all on this site, I am hoping someone here can help me in my journey to learning Access. I have some experience writing simple/moderate code in VBA for Excel, but I am now using Access to create a database to monitor/track progress on projects for an initiative at my work place.

My question revolves around using Combo Boxes. I have one table/form (tblEventSummary/frmEventSummary) where the user will enter data regarding a project they are working on. One field is called txtEventLeader. What I want to do is to create a combobox which will allow the user to either select a name from the dropdown list (of names that have been previously used) or to enter a new one. When entering a future form, I want any new names used to also be displayed in the combobox.

I was kind of successful doing this already using a query (qryEmployees), but if the same name is used in txtEventLeader, I then see that name twice in the combobox and in the associated query. I would appreciate any recommendations for what to do here. Hopefully I was able to ask a clear enough question...

View 2 Replies View Related

Repeating Rows In A Query?

Mar 27, 2014

I am attempting to perform a normalization process in a query where an organization would be operating in several different states and different countries. I have created three tables where: one table is Organization, second table is Country, and the third table is State. I made a table to record: Organization ID, Country ID and State ID. An organization could have 1 or more countries while having no US states. Also the organization could have 1 or more countries while having 1 or more US states.

What I am seeing is in my query is:

Organization | Country | State
Organization | Country | State
Organization | Country |

What I am wanting to see is:

Organization | Country, Country, Country | State, State

In the end, I want to make a mail merge from this query table.

How can I 'normalize' the query?

View 1 Replies View Related

Combo Box Value Is Repeating On All Forms

Dec 4, 2012

I have a form with a combo drop down box to select item from.The problem is once i've picked from a list this result shows up on all forms from #1 to end. I only need value picked to become record, only for that record.

View 1 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 Replies View Related

Import Repeating Data In Txt File

Dec 2, 2004

I have read lots about importing files but can't find anything on how to handle importing data that repeats itself in blocks from with in a txt file.

Note that I intend to run the import as an event each time the db is opened.

the data is an audit log that looks like this:

Date: 12-01-2004 Time: 11:16:57
Transaction #: 061318 Cashier ID: NB
Computer: COUNTER2
Sale Recorded. Transaction Total: 2.5

Date: 12-01-2004 Time: 11:20:55
Transaction #: 061319 Cashier ID: NB
Computer: COUNTER2
Sale Recorded. Transaction Total: 5

Date: 12-01-2004 Time: 11:59:48
Cashier ID: RC
Computer: COUNTER2
Deleted All Lines From Transaction

Date: 12-01-2004 Time: 12:01:17
Cashier ID: RC
Computer: COUNTER2
Opened Cash Drawer--NoSale

Whats the best way of doing this.

Cheers

View 4 Replies View Related

Auto Entry Of Repeating Dates

Aug 14, 2006

Hi,

On my form, we can enter time off for employees. This is working pretty well, but we have some employees that have flex days every other week or even once a month.

What I would like to make possible on my form, when the user is inputing the data, He makes the initial entry, and then he has an option (checkbox?) that they can select to make this same entry every (week, other week, or 1 month from the date). Have it repeat for a year out maybe, so if the user has every other friday off. It will make an entry for every other friday for the next year.

I've been searching the forum, and I've been reading on the dateadd function. But I'm not sure how to implement it on this form, not sure if there is a way to make it repeat for a year? Also not sure if it's possible (or if that's the best choice) to have checkbox for choices using it?

Any ideas suggestions?

Thanks
Chris.

View 4 Replies View Related

Repeating Data From Prior Record

Mar 16, 2005

I have users who use a form to enter data. One of the fields is "DateRange". The users tend to enter records in groups. The group of 10 records will all have the same "DateRange" As they start a new record is there a way that the "DateRange" field can copy from the prior record?
Any help would be great!

View 8 Replies View Related

Repeating Macro Needs To Stop After Last Record

Nov 1, 2006

I am basically setting up a macro that will change the value for a field from "N" to "Y" for all records from a query. The macro works fine except for it does not know when to stop and gives an error message of "You can't assign a value to this object" after it has gone through all of the records and there are none left to change. The macro is set up like this:

OpenForm Action : This form is populated by the query and I set the Where condition to open records that don't already have a value of "Y".

SetValue Action : I use this to set the value of the field to "Y"

Close Action : I use this to close the form and save the changes

RunMacro Action : This is where the problem is. I use this to repeat the macro so it can go through the rest of the records that still have the field set to "N". I need to set the Condition or Repeat Expression so that it checks if all of the records have been changed. It either needs to check that all records have the field = "Y" or check that the macro is on the last record. Basically something that will cause the macro to not open itself again when there are no more records to be changed.

View 2 Replies View Related

Forms :: Repeating Controls In A Form

Jan 7, 2014

I have a form that uses VBA codes to write all the info from the controls in the form into different relational tables. I was stopped at the point that I wanted to have repeating controls. Collect multiple instances of the same type of data, such as in the case of writing many people's Name, Age and Specialty in the Employees table. How do I do that? What is the VBA code for this task?

View 1 Replies View Related

Reports :: Chart Is Repeating For Every Record

Sep 22, 2014

My query has the fields setup ID, Date, Session, Value.In a report I am trying to create a bar chart that shows session names on the y axis and values along the x axis. I want to show 1 chart per ID.However, when I run chart wizard and create my chart I am getting charts for each ID repeating by the number of sessions (number of records in my query).So for example with 3 sessions im getting:

ID: 1 - Chart
ID: 1 - Chart
ID: 1 - Chart
ID: 2 - Chart
ID: 2 - Chart
ID: 2 - Chart
ID: 3 - Chart
etc.

Where the chart is setup the way I want it, but it repeats for each session.how I can get the report to show 1 chart per ID?I tried grouping by ID but didn't seem to work

View 3 Replies View Related

Tables :: Deleted Fields Not Free Up For Adding New Fields

Aug 15, 2013

I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?

View 8 Replies View Related







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