Solved Unsure how to title this, involves text boxes, only changing them if necessary (1 Viewer)

[donkey9972]

Hi again, As the title says I have no idea what I should have titled this to fit in the space allotted. I have started to make this, but it is no where near finished, it is mostly just thrown together at this point.

Okay, so here is the situation. I need to have 2 forms and a single report. Both forms based off of at least 1 table, might need 2. One of the forms is going to have the basic information that will change daily/monthly such as date, quantities of things, etc... The other form is going to be for updating information on the report that only changes when there is a new contract number or director etc... (which this only happens about once every 1-5 years.

That all being said, I need all this information on the single report, but how do I get the items that only change every 1-5 years to be on every report without updating it daily/monthly unless the information was changed on the update form?

 

[jdraw]

You should research Normalization and adjust your table structure to support your needs.
Often better to describe the business process involved in simple, plain English to readers so they can offer more focused responses. Also, many will build a model with tables and relationships (relationship view) and ensure their structure can support requirements.

 

[arnelgp]

i made the Report Unbound.
it will get the "latest" data from both tables.

 

[arnelgp]

binding the report to query2.

 

[Pat Hartman]

You need separate tables for the two kind of data.

You should use a bound report rather than an unbound one. Use a main report with a subreport or an unbound main report with two bound subreports depending on the relationship between the two tables.

 

[Pat Hartman]

Your tables are not set up correctly. For starters, naming your autonumber PKs "ID" is less than helpful. Always give them meaningful names so when you use them as foreign keys, someone looking at the table can figure out what the FK points to. Then you need to add a FK to the monthly report table to point to the correct company/canteen. You then will add a combo to the monthly report table so you can choose the company/canteen you are reporting on.

Then instead of using the random join suggested by arne, you can join on the actual FK to PK relationship. Once you do that, your form can be based on a query that joins the two tables and you can select the columns you want from each table.

If you run the report from the monthly report form, you can pass the ID of the monthly report to the query so it opens to the month and company/canteen you are looking at.

 

[donkey9972]

Thank you all for the help. I will take all the suggestions into account. Also thank you for the database upload. I have no doubt in the midst of all this it will all solve my issue.

 

[Pat Hartman]

Before you accept one of arne's databases, please add at least a dozen records to each table.

 

[donkey9972]

I have been playing around with this unbound and bound DB Mr. Arnel uploaded and I am having no luck on one thing. On the report I am unable to get it to sort by date. I have tried grouping and sorting on the report itself, I have also tried to sort within the query but I am having no luck, what am I doing wrong?

 

[Gasman]

Sorting the query is going to do no good whatsoever. It needs to be done in the report.
Unable to understand why you cannot sort, it just involves selecting fields and order?

 

[arnelgp]

the Question is, why sort?
the sample you showed is for a Single Cover page unless
you are printing the details.

 

[donkey9972]

Well believe it or not, sometimes people forget to enter information, and when I have the reports viewed I need to have them in a descending order by date. So when I have to go back or someone else goes back in and inputs the data it has to be in the correct descending date order.