How to search entire table through form

[NightSpy2]

Hi there I have a form with two unbound text boxes: HireMovieID and HireCustomerID, and a button HireButton which runs my query: HireHistoryQuery.

Then I have a table: HireHistory.
In my HireHistory table, I have my CustomerID's along the top as column names. Then the records for those columns are in this sort of format: "0001 on 19/05/2006" as type Text.

How can I make it so that when a user enters a Customer ID (e.g. 23) into my HireCustomerID box in my form, it shows column number 23 and all it's records?

Also, what code do I need so that if someone enters something into the HireMovieID text box in my form (e.g. 0001) it shows all of the instances of that from the whole table in its respective column?

I can post pictures or a copy of my database if needed.
Thanks in advance for your help!

 

[boblarson]

The very first thing I would say is to fix your bad table structure. I hate to sound mean (because I've been where you are many years ago and got the same talk), but your table structure is not properly designed and is definitely not NORMALIZED.

Your columns should NOT be names of customers. So we need to revisit and have you rework this or you will have something that will forever be a horrendous thing which you can never get meaningful data out of. You have a spreadsheet - not a relational database and that needs to change.

 

[NightSpy2]

Ok thanks.
Yea no problem, I know you don't intend to sound mean.

So how can I 'normalize' my database?

 

[boblarson]

I don't know what other fields you need so this is a very GENERAL reply.

Something like this:

tblCustomers
CustomerID - Autonumber (PK)
CustomerName
Address
City
Region
PostCode

tblHireType
HireTypeID - Autonumber (PK)
HireTypeDescription

tblCustomersHires
CustomerHires - Autonumber (PK)
CustomerID - Long Integer (FK)
HireTypeID - Long Integer (FK)
HireDate - Date/Time

That is a very general example.

 

[NightSpy2]

My Customer table is quite like you said, but I don't quite understand what you're doing with the HireType and CustomerHires.

I've uploaded an excerpt of my database, so if you could tell me more specifically what is wrong / how to fix it, that'd be great!

I know that having CustomerID's as column names is horrible, but I just can't think of any other way to make it work with a HireHistory? :banghead:

Sorry, I'm not very good at access

 

[boblarson]

I can't open the file here at work. I only have Access 2007 here and it would appear you have a newer version so I'll have to wait until I get home to check.