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

donkey9972

Registered User.
Local time
Yesterday, 16:18
Joined
May 18, 2008
Messages
13
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?
 

Attachments

  • Monthly Packet.accdb
    580 KB · Views: 111

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:18
Joined
Jan 23, 2006
Messages
14,425
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

..forever waiting... waiting for jellybean!
Local time
Today, 07:18
Joined
May 7, 2009
Messages
16,775
i made the Report Unbound.
it will get the "latest" data from both tables.
 

Attachments

  • Monthly Packet.accdb
    632 KB · Views: 106

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:18
Joined
May 7, 2009
Messages
16,775
binding the report to query2.
 

Attachments

  • Monthly Packet.accdb
    640 KB · Views: 109

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:18
Joined
Feb 19, 2002
Messages
36,836
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

Super Moderator
Staff member
Local time
Yesterday, 19:18
Joined
Feb 19, 2002
Messages
36,836
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

Registered User.
Local time
Yesterday, 16:18
Joined
May 18, 2008
Messages
13
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

Super Moderator
Staff member
Local time
Yesterday, 19:18
Joined
Feb 19, 2002
Messages
36,836
Before you accept one of arne's databases, please add at least a dozen records to each table.
 

donkey9972

Registered User.
Local time
Yesterday, 16:18
Joined
May 18, 2008
Messages
13
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

Enthusiastic Amateur
Local time
Today, 00:18
Joined
Sep 21, 2011
Messages
10,838
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

..forever waiting... waiting for jellybean!
Local time
Today, 07:18
Joined
May 7, 2009
Messages
16,775
the Question is, why sort?
the sample you showed is for a Single Cover page unless
you are printing the details.
 

donkey9972

Registered User.
Local time
Yesterday, 16:18
Joined
May 18, 2008
Messages
13
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.
 
Last edited:

Users who are viewing this thread

Top Bottom