Search Database, results to list then open form 1

[StuartBombay]

Thanks to direction received on this forum, I now have a customer form with an orders sub-form. They are bound forms, they can be used to look up, change, or add customer and/or orders.
The orders are plaques that can say just about anything on them. I would like to be able to hit a search button on the main form, fill in any part of what any plaque might say, get back a list of orders with that word/phrase. Then, double click each item in the list and have that order show up in my main customer/orders form.
I can picture what I want, but I don't have a solid plan on how to implement it.
The table structure is like this, they are linked by keys and (I think) the relationships are set up pretty solid. -
Company(one) ->
Customer(many) ->
Order(many) ->
Lines(many)
Can anyone provide guidance to get me started on this?

 

[TheAceMan1]

How are ya StuartBombay . . .

You need to include the PrimaryKeyName and any ForeignKeyName for each table, along with the relationships. Use the following format (where [blue]PK[/blue] means primarykey, and [blue]FK[/blue] means foreignkey). Include any other fields that apply to your schema to makes things clearer:

[tt][blue]TableName
*********
FieldName as datatype PK
FieldName as datatype FK to TableName.FieldName
FieldName as datatype
'
'
FieldName as datatype[/blue][/tt]
Now . . . am I understanding, there's a number of fields involved in a [blue]plaque[/blue], some of which have common static data?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[StuartBombay]

Right - much easier to understand the table structure that way.
Here it is -
Compnay
ComanyID as autonumber PK

Customer
CustomerID as autonumber PK
CompanyID as integer FK to Company.CompanyID

Orders
OrderID as autonumber PK
CustomerID as integer FK to Customer.CustomerID

Plaque
OrderID as integer FK to Orders.OrderID
Line1 as string
Line2 as string
Line3 as string

Yes, Line1, Line2 and Line3 are what I'm searching. I want to get all the matching orderID's back to a listbox, then double click on each one to display the main form which has the customer information. I think I know how to take it from the list box, it's the search itself that I'm stuck on.
Thank you -

 

[fneily]

Your Plaque table is not normalized. Violates first normal form protocols in many ways. Could be:
ID PlaqueID OrderID Line
1 ABC 3 Happy Birthday
2 ABC 3 Vice Pres.
3 ABC 3 Blah Blah
4 cba 7 Congratulations
5 cba 7 etc.

Now you can run a query testing Line using Instr() or as criteria Like "*" & [FindWhat] & "*"
to get all the records with that word or phrase which can then be traced back to the orderID. The double clicking is then easy to retrieve the actually order.

However, I don't think you answered theAceMan1's question: Are these sayings/words static? Used over and over? Like Happy Birthday? If that's what you mean, then you can have a table with all the possible words/phrases, eg. tblSaying
SayingID Description
1 You're Fired
2 Happy Mother's Day
etc

Now it's just a matter of making a simple junction table.

 

[StuartBombay]

My bad. The Plaque table has both static and dynamic fields. 3 lines max
OrderID Statement1 (static) Line1 Statement2 Line2
12 In memory of Fluffy (2 & 3 are optional)

To finish the table structure-
Plaque
OrderID as integer FK to Orders.OrderID

Statement
StatementID as integer FK to Orders.OrderID

I'm searching on the dynamic fields (Line1, Line2, Line3) only.

I didn't give the lines in the plaque table ID's because that table is the end of the line, so to speak. Should it have a unique (autonumber) key regardless?
Thanks you for the pointers. I've never built a project from the ground up before. I've only revised other departed contractor work. Needless to say I've seen every style imaginable and remain a bit confused as to what is correct access protocol.

 

[PHV]

remain a bit confused as to what is correct
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[StuartBombay]

Thank you PHV. By taking just a quick look at that document, I see that in thinking that only 3 lines will fit on a plaque I ignored the normalization rules and complicated my search issue. Point taken. I'll re-design my table and subforms correctly then re-visit the search, which will now magically involve searching only one field!

 

[fneily]

History - Ted Codd was the creator, founder and namer of Normalization, a protocol that is a necessity for Relational Databases of which Access is one. A company was based on his work.. it's called Oracle. You can read his original papers and try and understand the steps/forms of normalization. But, generally, if you follow the basic first 3 steps, you're tables will be pretty good. If you don't, well, as you can see, you'll run into many problems.
The first step states that all fields should be atomic, broken down into the lowest denominator, eg. FirstName, LastName AND no duplicate column headings. This is the hard one for people to see and understand. You have Line1, Line2, Line3. Drop the number then you have Line, Line, Line. Duplicates. So what? Well, as you state, you may or may not have data for each Line field. The result is alot of nulls. Not acceptable. Also, what happens in the future if you want to add Line4? Line299? Add more columns? Wrong. This leads to two things: variable length records and a table within a table. Let's say Line1 field has data but Line3 doesn't. A table MUST be perfectly rectangular. Draw a line down the right side next to where the data ends. The right border would be jagged. Not rectangular. Reason, duplicate column headings and nulls. Also, Line1, Line2, Line3 sounds and looks like a table. It is. By Codd's own words, a Table within a Table. Not allowed. Period.
You'll notice if your table was set up the way I suggested, finding "Fluffy" would be quite easy.
The PHV reference is a good place to start. These are not intuitive ideas. It takes awhile to get a good grasp around it.
Enough preaching.

 

[StuartBombay]

I've read and contemplated, I feel close but don't quite have my head around it yet. Here is my new table structure:

Compnay
ComanyID as autonumber PK

Customer
CustomerID as autonumber PK
CompanyID as integer FK to Company.CompanyID

Orders
OrderID as autonumber PK
CustomerID as integer FK to Customer.CustomerID

Plaques
PlaqueID as autonumber PK
OrderID as integer FK to Orders.OrderID
DedicationID as interer FK to Dedication.DedicationID
Freeform as Text

Dedication
DedicationID as autonumber PK
Dedication as Text

So, you can end up with -
Company: Nuke Plant
Customer: Homer
Order: 1
In memory of - Fluffy
Sponsored by - Nuke Plant Staff

Order: 2
Dedicated to - The plant mice

Is the plaque table correct? Or should there be a separate table which links orders<>plaques<>dedications? I keep going back and forth on what's correct. If I get this last bit hammered into my skull I should have a relatively easy time with the rest of my project.
Thanks again for the instruction and articles. I volunteered to put this together, now I'm questioning my sanity.

 

[StuartBombay]

--continued. The tables look good, I have lookups on them so when I expand the company I see the customers, customers see orders, orders see lines of the plaque.
The forms look good MainForm = Customer, Sub1 = Order, Sub2 = Plaque. (Sub2 is a subform of Sub1).
I can open the form and add an additional line to an existing order/plaque. But when I try to create a new customer and add lines to the plaque, I get an error stating that the primary key cannot be null. Primary key for the Plaque table is OrderID + PlaqueID. If I remove the primary keys, then I don't get an error... but I don't get any lines for the plaque either!

 

[fneily]

Speech - Company Info is fixed, Customer info is fixed, Dedications are fixed, and Plaque info is fixed. You never mentioned what type of plaque, size, material. So I'll assume you're just like a trophy shop and need this info for a plaque. Those tables would look like:
Company
CompanyID
Info fields

Customers
CustID
CompanyID
CustInfo fields

Dedications
DedicationID
Description

Plaques
PlaqueID
Price
Size
Material
Etc.

Now someone makes an order. So the above information must be brought together. But how? Well, a customer can have many orders. And it is possible that an order may be for many customers(two people retiring). So you have a many-to-many relationship. In a relational database, this is messy. So you create a junction table. In this case that would be your Order table. So,

Orders Multi-field PK on OrderID/CustomerID
OrderID
CustomerID
PlaqueID
Date
Labor_Cost

On your entry form, the ID's can be dropdowns, date can be defaulted, Labor_cost if not a standard.
Note: Total cost is not stored. Completion Date, if calculated would not be stored. They are calculated in the form or report.

But a plaque for an order may have, as you stated, between one and three lines. So you'd have an OrderDetails table.

OrderDetails
OrderDetailID
OrderID
DedicationID
FreeformText

Again, this way an order can have one, two, or three lines without dealing with null fields. Normalized.

And if you look at the above tables, the info you want as a report falls right out by connecting the tables in a query. Company name, customer name, Order number, Plaque type, and Dedication/Freeform lines on that plaque for that order.

 

[StuartBombay]

Thank you very much fneily, I'll get this down eventually. You're right, not enough information on my part. This project is for an animal shelter. A sponsor makes a donation then gets a plaque on certain cage in a certain room. We need to track who's plaque is where, when it's 6 month limit is up (so we can call to get a renewal on the donation), and where the plaque is.
I've studied your last post, and reworked the tables as follows:
Company
CompanyID
(info)

Sponsor
SponsorID
(info)

Romm
RoomID
Description

Dedication
DedicationID
Description (in memeory of, sponsored by...)

Plaque
PlaqueId
(room or cage - More $$ for sponsoring a room)

Orders
OrderID PK
SponsorID PK
PlaqueID
RoomID
..dates and $$ and a memo field

PlaqueDetails
PlaqueDetailID
OrderID
DedicationID
Freeform

I'm hoping I understood your instruction correctly. I've volunteered to do this so they pretty much have to be patient with me. However, I want to give them something that's useful.
A star for your patience...

 

[fneily]

Don't forget to put CompanyID in the Sponser table.
So you take an order and assign a sponser, a plaque and a roome to it and other common info such as date taken. Then what'll be on a plaque, one to three lines, is in the detail table. Flows pretty good.
The only change I would make is to put the indicator for a cage or room in the Order Table. The plaque table is just about plaques, not where they're located. Location depends on the request of the Order.
Besides that, now it's time to create the tables in Access, fill in the data, make some forms, queries and reports.
Simple, yes?
But, an important question - do you understand why the tables are designed as they are now? If you go back and read about normalizing, you'll see that with your table structures, you have only one place for company info, one place for sponser info, one place for plaque info, etc. Concise and tight data. Not like Excel where a sponser's name would be written in many rows.
Onward.

 

[StuartBombay]

Yes, thank you. I have the CompanyId in the sponsor table. Thanks you for the correction on the placement of the room. I had wondered about that.
I've also created combo boxes in the lookups in the tables and when I opened up the relationship window ... they magically related to each other perfectly! Then, I did an auto-form on orders which automatically included plaques as a sub table that has the plaque details as a continuous table. Then did an auto-form for sponsors and replaced the orders sub-table with the orders sub form, beautiful. That main form has an unbound company combo that fills an unbound sponsor combo which fills up the form with the appropriate sponsor and order data.
Much more to do, but I'm confident that the rest will be painless do to solid table design.
I can't thank you enough for helping with the table structure. That is invaluable education. I have a snapshot of my relationship window and a copy of the Cobb article for future reference.
And yes, they had originally asked for a spreadsheet...