Access Help!

[seenfresh]

I am a ROOKIE with Access and I have a field that is used for inserting data (Advertiser Name "AN"), and a following field that includes a pivot table that is used to select the (Category of Advertiser) from a list of selections.

The (AN) field does not hold a query because It is used to list new (AN) data

Is it possible to develop a macro that will search the (CofA) pivot table for any full word match in the field(AN)

Hence, doing an automatic search for relative values so that I don't have to search manually in the pivot table?

Would Appreciate any help or advice

Thanks in Advance

Sean

 

[SydneyGeek]

Hi Sean,

Just to clarify: You want to select Category of Advertiser, and then pick an advertiser from another list filtered to that category. Is that correct?

If so, there is no need for a pivot table as part of the search. What you need is a 3-table setup that lets you link any advertiser to any category, combined with a form that lets you pick advertisers based on their category.

Table 1: Categories
Field:
AdCategory, Text, Primary Key

Table 2: Advertisers
Fields:
AdvertiserID, Autonumber, Primary Key
AdvertiserName, Text
Other fields (Address, Phone, etc). These should all be Text fields (including phone numbers).

Table 3: AdvertiserCategories
Fields:
AdCategory, Text
AdvertiserID, Number
These 2 should be set up as a compound primary key. That means that any one combination of advertiser and category will have to be unique.

If the concept of primary keys and data normalization is new to you, do a Google search on Normalization and run through some tutorials so you get the idea. Databases rely on you breaking down information to distinct categories, and never storing more than one piece of information in one field (for example, FirstName and LastName should be separate fields in a table, not lumped together in one Name field). They then use unique fields called primary keys to link data together when you build relationships between tables. Sounds complex, but it greatly simplifies data entry and searching.

Once you have that sorted out, come back with the selection question and we will help you with that. The reason for this is that, without understanding how to structure the database correctly, any design that you do on forms and reports will have to be redone when the database starts fighting you instead of helping you.

Denis

 

[seenfresh]

Thanks for your help!

I'll have to read and research this ... and will post back!

Cheers!