dates

[ka1924]

I created a form with 25 start dates and 25 end dates for each company . The user will enter the dates after the goods are delivered. I need to create a report based on each company's name on the start date and the end date .

The date fields are filled with some days in June and some days in July. The user needs to run a report on how many goods were delivered in June. How to create a parameter on the query?

ex: If the user enters the dates from June 01 to June 30th It has to print a report between those days.

Thanks in advance.

 

[Remou]

Can you explain that again, please. You say 25 start dates, are these 25 different fields in a table?

 

[ka1924]

Created 25 different start date and end date fields in a table.

 

[rjoubert]

And all 25 start dates and 25 end dates refer to the same thing, in regards to when goods are delivered? If so, it would be a much better table design if you had 25 records per company, with one start date and one end date field. What happens if you need to add a 26th start and end date? You would need to modify the table design...whereas if you had 25 records per company, you would just need to add another record.

 

[ka1924]

I don't need the 26th date. The goods are not develiverd for more than 20 dates for one company. I created 5 more dates to be on the safe side.

 

[Remou]

This is not good practice. You should look at:

http://r937.com/relational.html

http://www.alphabetcitydataworks.com/fundamentals.htm

(same as above, different format)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

You are already encountering some of the problems inherent in such a design. These problems will only get worse. It is better to redesign at an early stage than to wait for the whole thing to fall apart.

It may be possible to get out of your immediate difficulties with a Union query, but I am not sure.
UNION Operation

http://office.microsoft.com/en-us/assistance/HP010322791033.aspx?mode=print

 

[missinglinq]

Remou is right on the mark. You're trying to use a relational database (Access) but what you've designed is essentially a flatfile database!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[ka1924]

I have master table . That table stored company names and address. Detailed table is company id,dates and quantity. This 25 date fields are only for one year for each company. If new year starts the data is archieving into another table. I checked last 5 years data . Each Company recieved goods only in 15 dates, some of them received 20 dates per year.

Please let me know how to print report monthly report based on Start date and End date.

 

[randy700]

Obviously, the first step would be to follow the advice all ready provided, and redesign your database. Since you are unwilling to do that, you will need to provide some additional information, although I'm not sure there is a way to accomplish what you seek. From your post, it appears that your table has fields such as StartDate1, StartDate2, StartDate3.... StartDate25, EndDate1, EndDate2.... EndDate25. So, the first question is -- how do you know which start date is the first one in June?

Randy

 

[ka1924]

Redesign means how can I do please give me advise.

when user enter on parameter value start date and end date. It has to check all 25 dates where is the june 1st.

 

[rjoubert]

Reread the posts that we have all left you already...I believe everyone has already provided advice on redesigning your table. Have you checked out the links that Remou posted?

 

[Remou]

Have you looked at the Union query mentioned in my post?

It may be possible to get out of your immediate difficulties with a Union query, but I am not sure.
UNION Operation

http://office.microsoft.com/en-us/assistance/HP010322791033.aspx?mode=print

This is also relevant to a redesign, which should have at least a table of Companies and a table of Delivery Dates.

 

[rjoubert]

Replace your 25 start dates and 25 end dates with a new table like this...

CompanyID StartDate EndDate
123 1/1/2006 1/31/2006
123 2/1/2006 2/28/2006
123 3/1/2006 3/31/2006
...
456 1/1/2006 1/15/2006
456 2/8/2006 2/15/2006
...


This would make querying so much easier, as you would only be looking at ONE StartDate and ONE EndDate.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886