Still Having Problems... pls help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I changed a few things around in my table... however, because all of the
data needs to be retained, I'm having difficulty putting it in a way that can
be queried the way I want. Here's an example of the fields :

INSPECTno1requested
INSPECTno1DatePasssed
INSPECTno1reinspect
INSPECTno1infraction?
INSPECTno1Type
INSPECTno2requested
INSPECTno2DatePasssed
INSPECTno2reinspect
INSPECTno2infraction?
INSPECTno2Type
INSPECTno3requested
INSPECTno3DatePasssed
INSPECTno3reinspect
INSPECTno3infraction?
INSPECTno3Type

Etc... up to INPECT15 of every field. (This was done to track the info for
up to 15 possible inspections that can be given under one permit number.)
(Each row in the table has approximately 90 fields in total.)

With all of these fields, even when I enter the [Enter Date] in the query,
it pulls all of the data for the date I enter, but it also pulls all of the
data for the other fields (previous or future inspections). I need to find
a way to enter a date, and let it only retrieve the Type of Inspection,
Permit Number, and Date Requested (date I enter in the query). This seems
impossible - any ideas if this can be done, or if I should try another way of
setting up the table?

Help!
Thanks.

Barkaroo



BARKAROO said:
I have a table with the following fields that tracks the date an inspection
was requested/booked for each field respectively(formated in Medium Date) -
examples of fields are as follows:

Footingsrequest
Framingrequest
Insulationrequest

I have approximately 15 fields.
Every Row in the table is for a Unique permit number - each permit number
has the fields I mentioned above (as well as fields for address, owner name,
etc...) so that we can enter the date requested. Is there a type of query
(maybe a type of parameter query) where I can enter a date, and, if there is
that particular date in any of the fields, it will pull up the respective
row/info?

The final product would be that I could enter a date, and the query would
tell me all of the inspections for that date.
Hope this makes sense.

Any/all help is greatly appreciated.

Thanks.
Barkaroo.
--------------------------------------------------------
Click to expand...
Just put the criteria under each of the dates on a separate line. When you
are in design view for a query, you will notice that the criteria section at
the bottom of the screen contains many lines (rows) and each of these is an
"OR" condition...


Permit Number Footingsrequest Framingrequest InsulationRequest

[Enter Date]
[Enter Date]

[Enter Date]


BUT, It seems that your table design is flawed. I think you should only
have three fields...
PermitNumber
RequestDate
PermitType


Rick B
--------------------------------------------------------------------------------------


Rick B said:
It keeps reformatting my post. hopefully you get the idea.

P# FRQ FRQ IRQ
[]
[]
[]




Rick B said:
That last parameter should have been scooched to the right...

Permit Number Footingsrequest Framingrequest InsulationRequest
[Enter Date]
[Enter Date]

[Enter Date]
Click to expand...
Click to expand...
 
Normal design would indicate a table with the following fields:

PermitNumber (tied to other table where permit details are stored)
InspectRequested
InspectDatePasssed
InspectReinspect
InspectInfraction?
InspectType


If a permit has ten inspections, there would be ten records. If one had
five inspections, there'd be five records.

Then, you can work with the data properly.

This is a classic one-to-many relationship.

Rick B




BARKAROO said:
I changed a few things around in my table... however, because all of the
data needs to be retained, I'm having difficulty putting it in a way that can
be queried the way I want. Here's an example of the fields :

INSPECTno1requested
INSPECTno1DatePasssed
INSPECTno1reinspect
INSPECTno1infraction?
INSPECTno1Type
INSPECTno2requested
INSPECTno2DatePasssed
INSPECTno2reinspect
INSPECTno2infraction?
INSPECTno2Type
INSPECTno3requested
INSPECTno3DatePasssed
INSPECTno3reinspect
INSPECTno3infraction?
INSPECTno3Type

Etc... up to INPECT15 of every field. (This was done to track the info for
up to 15 possible inspections that can be given under one permit number.)
(Each row in the table has approximately 90 fields in total.)

With all of these fields, even when I enter the [Enter Date] in the query,
it pulls all of the data for the date I enter, but it also pulls all of the
data for the other fields (previous or future inspections). I need to find
a way to enter a date, and let it only retrieve the Type of Inspection,
Permit Number, and Date Requested (date I enter in the query). This seems
impossible - any ideas if this can be done, or if I should try another way of
setting up the table?

Help!
Thanks.

Barkaroo



BARKAROO said:
I have a table with the following fields that tracks the date an inspection
was requested/booked for each field respectively(formated in Medium Date) -
examples of fields are as follows:

Footingsrequest
Framingrequest
Insulationrequest

I have approximately 15 fields.
Every Row in the table is for a Unique permit number - each permit number
has the fields I mentioned above (as well as fields for address, owner name,
etc...) so that we can enter the date requested. Is there a type of query
(maybe a type of parameter query) where I can enter a date, and, if
Click to expand...
there
is
that particular date in any of the fields, it will pull up the respective
row/info?

The final product would be that I could enter a date, and the query would
tell me all of the inspections for that date.
Hope this makes sense.

Any/all help is greatly appreciated.

Thanks.
Barkaroo.
--------------------------------------------------------
Click to expand...
Just put the criteria under each of the dates on a separate line. When you
are in design view for a query, you will notice that the criteria section at
the bottom of the screen contains many lines (rows) and each of these is an
"OR" condition...


Permit Number Footingsrequest Framingrequest InsulationRequest

[Enter Date]
[Enter Date]

[Enter Date]


BUT, It seems that your table design is flawed. I think you should only
have three fields...
PermitNumber
RequestDate
PermitType


Rick B
-------------------------------------------------------------------------- ------------


Rick B said:
It keeps reformatting my post. hopefully you get the idea.

P# FRQ FRQ IRQ
[]
[]
[]




"Rick B" <Anonymous> wrote in message news:[email protected]...
That last parameter should have been scooched to the right...

Permit Number Footingsrequest Framingrequest InsulationRequest
[Enter Date]
[Enter Date]

[Enter Date]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top