Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 25th, 2006, 06:03 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

>=[forms]![frmReports]![txtDateFrom] And <=[forms]![frmReports]![txtDateTo]

this is the string i use for the query, so it works if i put it in on of the date fields to query, now if i put it in all the date fields it querys then nothing and show nothing now i had this working yesterday and all of sudden its not working now... i cant understand that as it worked and at least showed me the custmers within the date fields i queried...... man this is proofing to be a pain in the ass

 
Old August 25th, 2006, 07:02 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Hmmmm... judging from your last two posts, it suspect that your table is not normalized and you're not understanding placement of criteria.

If you have fields such as this Machine01, Machine02, ... , Machine20; and Type01, Type02, ... , Type20; Date01, Date02, ... , Date20, etc. then you're not normalized. You just need one field each called, say strMachine and strType and dtmDate, and another field called bytMachineNo (this one can be set to a number from 1 to 20).

Criteria need only be placed once in a query, not for "all the dates" as you suggested. Not knowing the structure of your table, query, and form will make it hard to solve cleanly... given my suspicions.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old August 25th, 2006, 01:19 PM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

The thing is each company owning these machines could have upto 20 plus i have to use a combo box thingy with the list of machines, if u dont mind i could send u the file over and u could then get a better idea of what im looking at....

be great if so

 
Old August 25th, 2006, 01:26 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What Greg is saying is that your table structure should look like this:

tblCompany
CompanyID - PK
CompanyName - text
Address1 - text
Address2 - text
etc

tblMachine
MachineID
MachineName - text
etc

tblType
TypeID - PK
TypeName - text
etc

tblJunction
JunctionID - PK
CompanyID - FK (combo box)
MachineID - FK (combo box)
TypeID - FK (combo box) (could also be in Machine table)
iDate - Date


This would allow you do have a company with a million (n number) of machines without having to redesign the table. What if a customer gets their 21st machine? In your design, you redesign the table. In this normalized design, you add another record. Always design for n records.



mmcdonal
 
Old August 26th, 2006, 01:37 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

Whats PK, FK mean, sorry im very new to access and need maybe complete tuition....

but for all your help its great gives me ideas and then i can try and put something better together

many thanks

 
Old August 26th, 2006, 09:41 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

PK is used to designate a Primary Key, and FK is used to designate a Foreign Key.

In a relational database, you might have a table of employees, and another table with multiple records that would be linked to that employee. In the employee table you would set up a PK field that contains a number (typically an autonumber) and then in the other table, instead of entering the employee name, you just enter the PK associated with them in the FK field. Designing your database like this dramatically decreases the size, and increases the performance of your database.

Hope that helps,

Mike

Mike
EchoVue.com
 
Old August 26th, 2006, 11:29 AM
Authorized User
 
Join Date: Aug 2006
Location: Berlin, , Germany.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Roe2000
Default

Helps greatly, thanks







Similar Threads
Thread Thread Starter Forum Replies Last Post
401.3 Access denied due to Access Control List cforsyth .NET Framework 2.0 8 May 28th, 2009 01:56 PM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 04:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 03:32 PM
ADE file in Access 2000 <---> Access XP ginoitalo Access 3 April 14th, 2004 09:06 PM
Access XP VBA compatibility issues w/ Access 2000 bourgeois02 Access VBA 1 August 19th, 2003 04:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.