Search Form - Noob really needs some help.

[CarysW]

Not sure what happened above but here's the question again:

I really am stumped with this now and I feel quite embarrassed as I keep asking but I really am teaching myself as I go along.

I need to create a complexish search form with two different kinds of search and I need to not only see the results but be able to create a report from the results.

I have a cut down version of my DB in a zip file in anyone fancies having a look but it is a bit of a mess.

 

[Kryst51]

Please post your DB and let us know more about the search you are trying to do.

 

[CarysW]

I've deleted the post with my DB in now as I didn't want to leave it there all night.

 

[Kryst51]

maybe you can delete all the records, and put dummy info in it, that way there wouldn't be sensitive info on the internet.

 

[CarysW]

I have changed the data as much as possible now without losing the essense of what I'm trying to do.....

 

[CarysW]

Anyone fancy having a go?

 

[Scooterbug]

I would take a look, but I dont have access 2007, sorry

 

[CarysW]

OK, have converted it to other formats, hope everyone can open one.

 

[Scooterbug]

I looked at it quickly, and I think that there are a few things in your table that need to be addressed first.

One, you have a lookup field at the table level. Those cause nothing but heartache. A quick search of the forums for table lookup will give you plenty of reasons to avoid them.

Two is the amount of Yes/No fields you have in your table. From what I gather, you need to be able to classify each business with certain attributes where the attributes are the yes/no fields? The first issue that I see doing it this way is that you have left no room for expandability. What happens, in the future, if a new attribute needs to be added? You will need to modify the table, update records and change queries and/or code to accomidate them.

Also, I noticed that you have your primary key set to Text. From what I can gather, you do this because each business can have multiple locations. What you should do is have separate tables for this information. One table to house the business name and information pertaining to the business and another table that lists the different locations and things related to each of those locations.

I think you should read up on database normalization. Searching the forums for that term will bring up an abundance of information on that. I think once your tables are normalized, you will find that searching for data will be much, much easier.

If I get some free time today, I can see what I can do with the db you provided.

 

[CarysW]

I looked at it quickly, and I think that there are a few things in your table that need to be addressed first.

One, you have a lookup field at the table level. Those cause nothing but heartache. A quick search of the forums for table lookup will give you plenty of reasons to avoid them.

Two is the amount of Yes/No fields you have in your table. From what I gather, you need to be able to classify each business with certain attributes where the attributes are the yes/no fields? The first issue that I see doing it this way is that you have left no room for expandability. What happens, in the future, if a new attribute needs to be added? You will need to modify the table, update records and change queries and/or code to accomidate them.

Also, I noticed that you have your primary key set to Text. From what I can gather, you do this because each business can have multiple locations. What you should do is have separate tables for this information. One table to house the business name and information pertaining to the business and another table that lists the different locations and things related to each of those locations.

I think you should read up on database normalization. Searching the forums for that term will bring up an abundance of information on that. I think once your tables are normalized, you will find that searching for data will be much, much easier.

If I get some free time today, I can see what I can do with the db you provided.

Thank you so much for having a look.

You see my problem is that I still can't get my head around some of the stuff you are saying and I feel like an idiot. I'm going to try and have a good read up on normalization tonight.

Thanks again.

 

[Scooterbug]

I think all of us can look back at our earlier db and shake our heads at em. I know I can. Just keep pounding away at it and asking questions here if you get stuck.

I went through your db and broke up your master table a bit. Not sure if it will fit your needs (as not knowing exactly what you're goal is with the database) but as you can see, I created a table with all the Headquarters businesses and the branch businesses. In the branch businesses, there is a field that stores the primary key of the HQ business. This way, you can reference the branches based on the HQ.

As for the Yes/No fields, not sure what each field represents so for me to break them up would be pure speculation.

 

[CarysW]

Thanks Scooterbug. It makes a little more sense now but you are right that I didn't really give enough info about my final database.

The Yes/No fields are (mostly) brands that we supply. The searches I will need to do will be (for example) stores that stock a certain brand in a certain county, I will need to know whether they have an actual store or site - these searches will be done including the HQs and branches as MOST of our customers have stores at both.

I also want to be able to search by Name(which will incorporate Business Name and Trading As), Town, Debtor number, search code and postcode(first few characters) these will all be typed searches but the results will show all of the information in the Master Table.

I will also need to do more general searches like all of our customers in a certain region, which will again show all of the info in the Master table.

I will also need to be able to print off reports of all of these searches.

From what I can understand - if I want to say search for a store stocking a certain brand(as the Yes/No fields) I should have a separate table for each brand showing stockists?

 

[Scooterbug]

Once the data is separated into different tables, the searches can be easily accomplished as well as generating reports.

What I would do with recording which store stocks which brands is to have 2 tables. I would also scratch having two separate tables for Branch and HQ since the HQ can also be considered a store as well. You can use a Yes/No field to denote if the store is a HQ or Branch.

tblBrands
BrandID Primary Key
BrandName

tblBrandStock
BrandStockID Primary Key
BrandID Foreign Key
StoreID Foreign Key

 

[Scooterbug]

CarysW,
Work was slow today...and I enjoy a good challenge. I worked on the db you provided..and got most of the kinks worked out for ya. I've attached what I've done so far.

It's not complete..as the advance search only uses the Brand Names, Country and Region for it's search results. I've commented in the code on how I worked it. Hopefully it can steer you in the right direction to add in the other fields you wish to use in the search. (I dont normally comment in code...bad habit I know.)

Any questions...feel free to ask. I wont be around this weekend..but i'm sure others will be.

 

[CarysW]

CarysW,
Work was slow today...and I enjoy a good challenge. I worked on the db you provided..and got most of the kinks worked out for ya. I've attached what I've done so far.

It's not complete..as the advance search only uses the Brand Names, Country and Region for it's search results. I've commented in the code on how I worked it. Hopefully it can steer you in the right direction to add in the other fields you wish to use in the search. (I dont normally comment in code...bad habit I know.)

Any questions...feel free to ask. I wont be around this weekend..but i'm sure others will be.

Thanks Scooterbug! I really appreciate that.

 

[CarysW]

D'oh, just realised that the Yes/No fields should be checkboxes and not Text