Form to input Criteria limits

[dragonfly0802]

Using: Microsoft Access 2007

Setup: I have an Access 2007 database with a single table containing the following fields:
Name
Street
City
State
ZIP
Purchase Price
Purchase Date (From and To)
Item Amount
Ship Date (From and To)
Item Cost
Purchase Code
Store Code
State Code

I have created a form to use as a lookup with fields in the top part of the form and a datasheet in the bottom half of the form containing the results of the query (the query uses the above fields for criteria)

The issue I'm having is that the criteria in the query is too long so it ignores some of the criteria when running the query.

My question - do you have any suggestions for a workaround to this issue? I need to be able to use all the above form fields for the query to perform the search and the user has to be able to enter partial values and date ranges.

I hope I have explained this clearly enough but if I haven't please let me know.

Thank you!

 

[PNGBill]

Does your database really only have one table ?

Try making more then one query.
2nd query will select first query and then do more work.

 

[boblarson]

Sorry to say, but that is a very, very bad design. So, if someone orders more than one item, you store all of their information again, and again as well as other redundant information? Your design needs to be normalized.

 

[dragonfly0802]

PNG Bill - Yes my database is a simple name and address storing database so there is only one table. I will try your suggestion of more than one query.

And to Boblarson - thank you but my data is already normalized. In each record there is only ONE city. What I'm trying to accomplish is to create a form just for the purposes of selecting criteria for the query results.

For Example: I have 25 records for users in Avenel and 25 records for people in Brick.

I want the user to be able to check boxes for Avenel and Brick and the results of the query to show the 50 records total.

Thank you for taking the time to reply. Any suggestions would be greatly appreciated!

 

[dragonfly0802]

Boblarson - a P.S. The sample above is just a sample of field names to make the example I'm trying to provide easy to understand. I don't want you to think I have a bad design

 

[boblarson]

And to Boblarson - thank you but my data is already normalized.

No, it is not. Ask any of the more experienced developers here and you will find that you are sadly mistaken. For what you have there you should have at least 3 tables. A table for customer information, an orders table and an order details table.

 

[PNGBill]

If your database is so simple then why not strip it of sensitive data, compact and repair and post it here.

Your query should be simple.

Select [fieldname] from [tablename] where [fieldname] =

Not correct syntax but very simple

But... first you need to have more then one table or you just have a list and why would you have a multiuser database with just one table??

Copy and past an sql view of your query.

 

[dragonfly0802]

Hi Bob;

Not sure if you got my follow up. You are taking my example too literally. I actually have 3 tables storing this information. One table contains the Customer information name, address, etc., another contains the purchase information, another contains the details per item. It is actually the query that gets me to the results you see above. I apologize for not setting this example up appropriately. I am well aware of how to normalize a database but I sincerely appreciate your input. Do you have any suggestions for the question I posed in my original post?

 

[dragonfly0802]

PNGBill - I will actually go ahead and do that. I appreciate your time! Thank you so much!

 

[dragonfly0802]

Thank you all for your input. I've found a way to work around this issue. PNGBill, you're suggestion to use multiple queries worked Thank you!