Help Creating Delivery Report - Week View

[neo__04]

Hi all,

Fairly new to creating work in access.

Im creating a database that we can enter jobs into.
A job will consist of 3 seperate parts, Called "Trusses", "Walls", "Posis"
They will each have a different delivery date and possibly a delivery time.

I am having trouble creating a report that gives me a week view, Monday to Friday, that shows what deliveries are on each day.

The main thing here is there is one job entry, with the 3 parts. So there will need to be 3 seperate entries on the report for each part of that job.

Any help would be great.
I'm not great with code, done most things with the macro builder in Access 2013.

I have attached a pdf of what I am looking to achieve.
Also attached the access database im working on

Thanks heaps

 

[plog]

This isn't a code issue, its a table structure issue. When you start repeating fields (or groups of fields) in a table, its time to move that data to its own table. You need to put the Trusses/Walls/Posis data in a new table.

All the fields in the Jobs table that are prefixed or suffixed with Truss/Wall/Posi need to go into a new table with an additional field to designate if that record's data was for a truss, wall or posis. I also believe, you have redundant/unnecessary fields. Tell me what the purpose of each of these fields is:

Trusses
DateTrusses
Trusstimecheck
Trussdeliverytime

 

[neo__04]

Hi,

Thanks for the details.
There are some fields i will work on over time, I'm just getting main functionality working first.

As for the fields you listed,
Trusses is a checkbox for us to click if we are supplying "Trusses"
Date Trusses is a textbox to pick a date
Trusstimecheck is a checkbox to check if there is a specific time for the delivery
Trussdeliverytime is a textbox we can put text in for the delivery time (eg. "8am")

So if i move the info for trusses/walls/posis into a new table,
What information would actually be in there?

Eg. Do i relate all the info in the new table to the ID field in the jobs table?
So that for each ID (Job) there are multiple parts and dates?

Sorry very new to this, but your being a great help!

 

[plog]

The first thing to do in any database related system is to structure your tables properly. That process is called normalization (http://en.wikipedia.org/wiki/Data_normalization), give the link a read.

It sounds like both your checkbox fields aren't necessary. You don't set up a field to tell you if another field has data--you just check that second field for data. What I still don't understand is if your date and time fields are related. Does Trussdeliverytime occur on DateTrusses?

In every case, you need a new table. Let's call it Deliverables and this would be its structure:

Deliverables
Deliverable_ID, autonumber, primary key
ID_Job, number, foreign key to Jobs table
Deliverable_Date, Date/Time field, to store both date and time of delivery
Deliverable_Type, text, will tell you if it is a Truss, Wall or Posis

So yes, this new table will link back to the Jobs table via Jobs.ID. Also, notice that I moved the Wall/Truss/Posis out of the fields' names and made seperate field in the new table to tell you what each record is for.

 

[neo__04]

Thanks again for more info.

Yes, Trussdelivery time and datetrusses are on the same day. I just done them seperately.
But im guessing by your mention about, you can do both date & time on the one entry.

I'll redo the table as you have listed it and get back to you when i get stuck

 

[neo__04]

Ok, I have made the table as you suggested.

I'm still confused as to how i now set the form up to save all the information to the different tables.

Can you have a look at the updated access file and point me in the right direction from here.

Thanks heaps for your help

 

[plog]

What you do now is make a continous form based on deliverables. Then you use that form as a sub-form on the Jobs form. Here's a link explaining the theory and practice of it: https://support.office.com/en-za/ar...9cb-831a-1e74d6f5f06b?ui=en-US&rs=en-ZA&ad=ZA