Add records to table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Add records to table

Brent Davis

3 years ago

I have a table that stores records for shipment data by customer. The fields are shipments, revenue, endofmonthdate, claimsratio, weight, and transit time). The data is downloaded from an excel spreadsheet and is monthly summary data. Currently, if a customer does not have info for a particular month a record is not created. The records are sorted by endofmonthdate so if there is not data for 4-30-2023 the form will show 3-31-2023 and 5-31-2023 data. I want a record for 4-30-2023 with the fields with zeroes in the form. The table currently has four years of data.

How can I add the non existing records by customer into the table? I assume with an append query but not sure how to set it up. Any assistance is appreciated.

Scott Axton @Reply

3 years ago

Brent  -
I'm generally not in favor of adding a "zero" record.  It can throw off or skew your data results.
It might be a case of just changing a query to a left outer join.  See:  Outer Joins

On the other hand, I can kind of see where you might want to have a list like you describe.
In your case, instead of looking for a customer that has shipments you are looking for those without shipments.
Once you get the list with out shipments you can use that as the source of data to add your zero records.
Check out the Have Not Ordered video.

Brent Davis OP @Reply

3 years ago

I am still having difficulty figuring this one out. From the screenshot below, account 67001129 has records for each month except July 31, 2021. My query is a left join from AccountNumber from the CustomerT and Monthly from the ShipmentT. I want July 31, 2021 to show as zero so my monthly growth/change calculation will compute. Because there is not a record for that date I cannot get a growth/loss percentage and it will not show on the query results if I am comparing July 31, 2021 and July 31, 2022 results. I hope that makes since. As always, any assistance is appreciated.

	Brent Davis OP @Reply 3 years ago

	Kevin Robertson @Reply 3 years ago
Missing Months

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View