Access Help!

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
Thanks for your help!

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

Cheers!
 

Forum statistics

Threads
1,137,335
Messages
5,680,893
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top