Query based on table check boxes, and input from a form

[superfly5203]

I'll try my best to explain what I'm trying to do, I hope it makes sense.

My database keeps track of 3 different contacts, and each of these contracts can have 5 different modification types. I have a form, "change entry form" in which all of my data flows into on table, "table 1". The different contracts are selected via combo boxes, and the different types of modifications are input via check boxes.

I set up an unbound form, "change query form" where a user can input the data they want the query, "change search" to run, via a command button. I got it to sort by dates no problem, but then when I added a check on my change query form so the user could say they want to look at one of the mod types, all I get out is a blank query screen.

When I'm done, I would like to have the change query form set up to ask for the date ranges, the contract, and as many of the modification types as they want (not all mod types are mutually exclusive). So possibly the user could say, I want to see contract A and Contract B, with modifications within this date range, and of these modification types.

Is what I'm trying to do even possible? If anyone has any ideas I would appreciate it.

 

[jdraw]

What are the fields in your table? what fields do you want users to search by/for?
What are users going to do once they find the "contracts" being searched?
Tell us more about the business behind this proposed database and form. Use simple English like you were talking to a six year old. Readers have to know the context in which all this is to happen.

 

[superfly5203]

my table that has all the data in it has about 40 fields. There are fields for basic info for the type of contract change, i have combo boxes for peoples names who are working on the change, and which contract the change is for. There are checkboxes if the change is an overlimit, an underlimit, a UCA, a PIO, and for misc changes. Then I have lots of fields for dollar values, (price, cost and profit) for what was proposed, our objective, our max, and what we settled at.

I envisioned that the user will open up a form and there will be options to select the contract, when the change was completed, what type of change (maybe UCA, PIO, and overlimit changes). Here are a couple examples:

1. Contract A, date ranges 1/1/15 to 1/1/16, overlimit change, UCA, was worked by Jim
2. Contract A and B, date ranges 1/1/15 to 2/1/15, all misc changes, worked by anyone

After the user enters the data they want queried, they hit a command button and the query runs and they have a list of everything that matches their criteria.

 

[jdraw]

Use simple English like you were talking to a six year old.

Users Contracts ChangesToContracts TypesOfChanges UCA PIO ???

If you can not explain/describe your requirement in simple terms, then you probably haven't done sufficient analysis to design your database.

Good luck with your project.

 

[superfly5203]

I didn't think it would help to know the definition of these terms, as I don't know how anyone knowing what a UCA or PIO really means would help with the database. They are just different types of contract modifications.

If you want an easy to understand analogy I guess you can think of a contract like a car manufacturer and a modification types like a model. Like a Ford sedan 2 door or a Ford truck or Honda sedan 4 door

So in the end the user would say I want to see all Ford and Honda sedans that have four doors that were manufactured in 2015.

Sorry, I didn't think the definition of the terms mattered, if you want me to spell them all out i can, but I don't see the benefit of doing so.

I do appreciate your help

 

[jdraw]

Put yourself in the position of any reader. What is the subject of this proposed database? What is the issue that a database is going to solve/support? What are the things involved?
Is the description/definition of the business clear, or is it full of jargon?

Clarity of the requirement is KEY to getting a database designed to meet that requirement.

I realize you don't have many posts on this forum, but if you read through a few random posts, I think you'll see that many threads go on for 9-10 posts trying to figure out what the OP means.

Take a quick look at this material from Barry Williams.

Spend 30-45 minutes and work through this tutorial from RogersAccessLibrary.
Review the Hernandez method -it's fundamental to database design.
You have to do the work, but you will learn a process that can be used with any database and any DBMS. There are excellent requirements statements and a clear , repeatable process.

Good luck.

 

[superfly5203]

Thanks for those links it really did help me out. I think i'll go do some more research and ask questions on some basic database structure in another forum section as I don't think mine is laid out in an effective manner any more.

 

[jdraw]

superfly5203,

Here is more info. There are 8 videos on relational database starting here.

Watch 1,2 and 4 to get started. I'm sure you'll find them helpful.

Good luck.